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 ]</font>

Thanks for clarifying that Aladin.

Best regards,