ExcelBanter - FORMULAS please help

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - FORMULAS please help (https://www.excelbanter.com/excel-discussion-misc-queries/1121-formulas-please-help.html)

FORMULAS please help

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

One way

=SUMPRODUCT(--(A1:A10010000),--(A1:A10000<12000),B1:B10000)

Regards,

Peo sjoblom

boogie wrote:
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

On Thu, 2 Dec 2004 19:39:05 -0800, boogie
wrote:

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

=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