FORMULAS please help

B

boogie

Hi there
I am trying to create a formula that refences <> numbers in a columm then
adds up the from a different columm
i.e add b1:b100 only if a1:a100 is >10000 <12000 =46
a b
10000 32
9000 64
12001 86
11999 14
9990 12
 
P

Peo Sjoblom

One way

=SUMPRODUCT(--(A1:A100>10000),--(A1:A10000<12000),B1:B10000)


Regards,

Peo sjoblom
 
R

Ron Rosenfeld

Hi there
I am trying to create a formula that refences <> numbers in a columm then
adds up the from a different columm
i.e add b1:b100 only if a1:a100 is >10000 <12000 =46
a b
10000 32
9000 64
12001 86
11999 14
9990 12
Click to expand...

=SUMIF(A1:A100,">10000",B1:B100) - SUMIF(A1:A100,">=12000",B1:B100)

But your result is only 14. Your query specified that the numbers in colA be
greater than 10000 and less than 12000.

If you want a result of 46, you need to specify "equal to or greater than
10000":

=SUMIF(A1:A100,">=10000",B1:B100) - SUMIF(A1:A100,">=12000",B1:B100)


--ron
 
Top