If sum

[sjbutler69]

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

 

[Special-K99]

=a20*if(sum(a14:a20)<4741852,b6,if(sum(a14:a20)>70000000,b8,b7)))

 

[a massive zebra]

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"}))

 

[sjbutler69]

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

 

[sjbutler69]

Thank you. You both have been helpful.

a massive zebra - yours was the winner.

 

[billszysz]

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