# If sum

#### sjbutler69

##### New Member
Need a formula for IF SUM

If the sum of A14:A20 is less than 4,741,852 than multiply A20 by B6
If the sum of A14:A20 is greater than or equal to 4,741,852 than multiply A20 by B7
If the sum of A14:A20 is greater than or equal to 7,000,000 than multiply A20 by B8

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
=a20*if(sum(a14:a20)<4741852,b6,if(sum(a14:a20)>70000000,b8,b7)))

This method would avoid using nested IFs, which can get messy if lots of layers of nesting are required. =A20*INDIRECT(LOOKUP(SUM(A14:A20),{0,4741852,7000000,999999999},{"b6","b7","b8"}))

Last edited:
Sorry should have made that a bit clearer.

If the sum of A14:A20 is less than 4,741,852 than multiply A20 by B6
If the sum of A14:A20 is between 4,741,852-6,999,99 than multiply A20 by B7
If the sum of A14:A20 is greater than or equal to 7,000,000 than multiply A20 by B8

Thank you. You both have been helpful.

a massive zebra - yours was the winner.

=lookup(sum(\$a\$14:\$a\$20),{0,4741852,7000000},\$b\$6:\$b\$8)*a20

Replies
3
Views
346
Replies
3
Views
92
Replies
4
Views
178
Replies
2
Views
132
Replies
2
Views
274

1,219,518
Messages
6,148,747
Members
450,832
Latest member
Tyroneb90

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back