How to do the comand IF THEN in Excel

Question

=SUM(IF(E(H39<=38;H39>=20);1;0);IF(E(H39<=38;H39>=21);1;0);IF(E(H39<=38;H39>=22);1;0);IF(E(H39<=38;H39>=23);1;);IF(E(H39<=38;H39>=24);1;);IF(E(H39<=38;H39>=25);1;);IF(E(H39<=38;H39>=26);1;);IF(E(H39<=38;H39>=27);1;);IF(E(H39<=38;H39>=28);1;);IF(E(H39<=38;H39>=29);1;);IF(E(H39<=38;H39>=30);1;);IF(E(H39<=38;H39>=31);1;);IF(E(H39<=38;H39>=32);1;);IF(E(H39<=38;H39>=33);1;);IF(E(H39<=38;H39>=37);1;);;IF(E(H39<=39;H39>=38);1;))*5,38

I want to multiply a number in this case between 39 and 20, for a result of 5.38, but the range might vary; for instance, it could be between 33 and 20, 44 and 20, 90 and 20, 22 and 20, etc., thus I need an automatic code that works. Here, the code sums 1 for each IF (E..), allowing it to get a result between 1 and 15.

In order to sum up all the numbers in casel H39 that are greater than 20, I would like a straightforward and quick code.

Answer 1

Try this:

=SUM(IF(AND(H39<=38,H39>=20),1,0),...

If so then maybe this is what you're after.

=SUMPRODUCT((A1<=38)*(A1>={20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39}))*5.38

You might substitute an INDEX() formula for A1 in the formula above to alter the row you are viewing using a number in another cell, such as in cell B1.