Thanks:  0
Likes:  0

1. 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?

2. 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 ]

3. 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,

4. 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.

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

good luck

6. =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".

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

7. 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 ]

Best regards,

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•