How to do the comand IF THEN in Excel

0 votes
=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.

Mar 28, 2023 in Others by narikkadan
• 63,600 points
561 views

1 answer to this question.

0 votes

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.

answered Mar 28, 2023 by Kithuzzz
• 38,000 points

Related Questions In Others

0 votes
1 answer

How do I set the default paste special in excel to paste only values

I paste the values with a keyboard ...READ MORE

answered Nov 6, 2022 in Others by narikkadan
• 63,600 points
3,524 views
0 votes
1 answer

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,600 points
1,060 views
0 votes
1 answer

How do I stop python from appending data to the same row in excel?

There is no indication in your code ...READ MORE

answered Mar 25, 2023 in Others by narikkadan
• 63,600 points
553 views
0 votes
1 answer

Excel - How do I round a date type to the next hour if it is more than one minute

Add almost 30 minutes and it'll get ...READ MORE

answered Mar 27, 2023 in Others by narikkadan
• 63,600 points
390 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

Feb 18, 2022 in Others by Edureka
• 13,690 points
432 views
0 votes
1 answer

IF function in combination with an Round function Excel

I'm not sure if it is a ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 63,600 points
3,625 views
0 votes
1 answer

Excel If field with rounding

Here's your formula. Assumes your cell containing ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,600 points
833 views
0 votes
1 answer

IF formula to compare a date with current date and return result

You can enter the following formula in ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,600 points
1,052 views
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,000 points
1,797 views
0 votes
1 answer

How to create arrow buttons in Excel to scroll the table left and right?

Ctrl-shift-home goes to the upper-left of all ...READ MORE

answered Mar 26, 2023 in Others by Kithuzzz
• 38,000 points
844 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP