Sumif formula

[lars]

How do I get a sumif formula or something like it to work for the following scenario

Here is my data in column a and b

1 100
2 50
3 70
4 79
5 85
6 60
7 78
8 90
9 100

I want to add anything greater then 3 and less than 8 by looking at column "a" then adding the numbers in column "B"....the result would be 302 (79+85+60+78)

Help?

[Russell Hauf]

On 2002-03-28 14:23, lars wrote:
How do I get a sumif formula or something like it to work for the following scenario

Here is my data in column a and b

1 100
2 50
3 70
4 79
5 85
6 60
7 78
8 90
9 100

I want to add anything greater then 3 and less than 8 by looking at column "a" then adding the numbers in column "B"....the result would be 302 (79+85+60+78)

Help?

Try this:

=SUMPRODUCT((A1:A9>3)*(B1:B9))-SUMPRODUCT((A1:A9>=8)*(B1:B9))

This sums all the numbers in column B that are greater than 3, then subtracts the sum of all numbers greater than or equal to 8, giving you 4-7.

[ This Message was edited by: Russell Hauf on 2002-03-28 14:27 ]

[Barrie Davidson]

On 2002-03-28 14:23, lars wrote:
How do I get a sumif formula or something like it to work for the following scenario

Here is my data in column a and b

1 100
2 50
3 70
4 79
5 85
6 60
7 78
8 90
9 100

I want to add anything greater then 3 and less than 8 by looking at column "a" then adding the numbers in column "B"....the result would be 302 (79+85+60+78)

Help?

If you enter this formula as an array formula (instead of ENTER, press CTRL+SHIFT+ENTER) you will get the desired result.

=SUM((A1:A9>3)*(A1:A9<8)*(B1:B9))

Note, this will slow down spreadsheet calculations if you are evaluating a lot of data.

Regards,

[Barrie Davidson]

Try this:

=SUMPRODUCT((A1:A9>3)*(B1:B9))-SUMPRODUCT((A1:A9>=8)*(B1:B9))

This sums all the numbers in column B that are greater than 3, then subtracts the sum of all numbers greater than or equal to 8, giving you 4-7.

Much better answer than my Array Formula! Thanks Russell.

[IML]

One more:
=SUMIF(A1:A8,">"&3,B1:B8)-SUMIF(A1:A8,">="&8,B1:B8)

good luck

[Aladin Akyurek]

=SUMPRODUCT((A1:A9>3)*(B1:B9))-SUMPRODUCT((A1:A9>=8)*(B1:B9))

Much better answer than my Array Formula!

No, Barrie. They are exactly equivalent in behavior and, I think also qua cost. The difference is that you don't have to worry about control+shift+enter at entering and at editing with the SUMPRODUCT version.

Besides, Russell had better formulated it as:

=SUMPRODUCT((A1:A9>3)*(A1:A9<8)*(B1:B9))

or

=SUMPRODUCT((A1:A9>3)*(A1:A9<8),(B1:B9))

He probably had SUMIF in mind who knows .(See IML's post for the SUMIF version, which I'd prefer in cases of multiconditional summing with "between".

Aladin



[ This Message was edited by: Aladin Akyurek on 2002-03-28 14:46 ]

[Barrie Davidson]

On 2002-03-28 14:45, Aladin Akyurek wrote:


=SUMPRODUCT((A1:A9>3)*(B1:B9))-SUMPRODUCT((A1:A9>=8)*(B1:B9))

Much better answer than my Array Formula!

No, Barrie. They are exactly equivalent in behavior and, I think also qua cost. The difference is that you don't have to worry about control+shift+enter at entering and at editing with the SUMPRODUCT version.
kyurek on 2002-03-28 14:46 ]

Thanks for clarifying that Aladin.

Best regards,