How do I sum an array with multiple condition??

[flutie1668]

Hi

Currently I'm trying to figure out how do I sum up a row of number by using a multiple condition. What I had is (A2:AF202) a list of number range from 0-31. And I want to sum (A3:AF203) according to the condition I set on (A2:AF202) which is ">7" and "<=20".


2 A2 B2 C2 D2 E2 F2 .......................AF202
3 31 3 10 6 8 30 ....................... 1



I try SUMIF(A2:AF202, ">7" & "<=20", A3:AF203) but it doesn't work. Any Ideas??

[dominicb]

Hi Flutie1668

You're on the right lines with your formula, but you really need an array formula. Try using the formula below, but to commit it do not press enter, but ctrl + shift + enter. This will place curly braces round it and be defined as an array formula - and should answer your question nicely.

=SUM(IF($A$2:$AF$202>7,IF($A$2:$AF$202<=20,$A$203:$AF$203,0),0))

HTH

DominicB

[olasa]

Just as an alternative:
=SUMPRODUCT((A2:AF202>7)*(A2:AF202<=20)*(A3:AF203))

Ola Sandström


Note:
The formula will calculate each row by multiplication and then add each row sum
True*True*8-->8
True*False*8-->0
=SUMPRODUCT(8+0+.....15) --> ...

[flutie1668]

Thanks guy, it works beautifully now.