How can you use SUMIF with auto filtering? 1

[Buzzard7]

I have a spreadsheet with auto filtering. I want to use the SUMIF function, but when I do, it sums the entire spreadsheet and not what I have filtered. Is there a way to do this?

 

[luceze]

Use the subtotal function instead.

=subtotal(9,Range)

When you filter, it only calculates the visible range. Otherwise you will have to use the sumproduct function or an array formula.

HTH,
Eric

 

[Buzzard7]

Subtotal will not do the same function as SUMIF.

ex. =SUMIF(A:A,A1,B:B)
a b

1 3 1.00
2 4 2.00
3 3 1.50
4 2 3.25
5 4 5.00

I just want to sum the 3's in column a. Column b has a filter that is equal to or is greater than 1.00.

 

[xlbo]

SUBTOTAL is the only way to do filtered sums and counts. To do multi criteria sums and counts, you need to use SUMPRODUCT

=SUMPRODUCT((A2:A1000=3)*(B2:B1000>=1)*(A2:A1000))

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[KenWright]

As Geoff said, SUMPRODUCT is needed to calculate data as a filter would show it, but if you are using filters and you have filtered to what you need, then I don't see why SUBTOTAL will not do as you ask. Theoretically you have already filtered to show just what you need, in which case being able to sum the visible data should accomplish this, hence the use of the SUBTOTAL function?

Regards
Ken.....................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

Ken - I think he only wants the sum of the 3s where it is >=1 - at the moment, there are other numbers apart from the 3s in there. However, this could be accomplished by filtering on the 3s after the >=1 and then applying the subtotal

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[KenWright]

I was pretty sure he just hadn't filtered down to what he needed, and it may be he just hasn't explored the custom filter options.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Buzzard7]

That was just an example. I am working with a template that the data changes every day. I have the worksheet filtered by a range of days.

Ex. #2

Filtered < 3/25/2004 and > 3/19/2004
a b c

apples .50 3/20/2004
oranges .75 3/20/2004
apples .70 3/21/2004
lemmons .60 3/22/2004
oranges .85 3/23/2004
apples .85 3/24/2004




I want to know the sum of the apples. (apples = 2.05) This has been filtered by date already, but the filter will change the next day.

 

[xlbo]

k. - so what is your question ???
You've just been given the answer - which is basically that you can use SUMPRODUCT (but you must include the filter arguments as one of the conditions)
OR - you can filter the data a bit more and use SUBTOTAL

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Bowers74]

Utilizing SUMPRODUCT() as below will give you the answer you are looking for (based on your example data):

=SUMPRODUCT(((A1:A6="apples")*((C1:C6>DATEVALUE("3/19/2004")-(C1:C6<DATEVALUE("3/25/2004")))*B1:B6)))

But, you don't need to filter your sheet by ANYTHING. You an have SUMPRODUCT() do that for you. Place your criterea in a cell (eg. D1) and then the lowest date in D2 and the highest date in D3. In cell D4 you enter the formula:

=SUMPRODUCT(((A1:A100=D1)*((C1:C100>=D2)-(C1:C100<=D3))*B1:B100)))

This is assuming that your data is located in the range A1:C100. The formula will only return the total "sales" for the product in D1 if the date is between the two dates in cells D2 and D3.

I hope this helps!


Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[Bowers74]

[tt]
| A | B | C | D
-------------------------------------------------
1 | apples 0.5 3/14/2004 apples
2 | oranges 0.75 3/15/2004 3/20/2004
3 | apples 0.7 3/15/2004 3/24/2004
4 | lemmons 0.6 3/16/2004 2.05
5 | oranges 0.85 3/16/2004
6 | apples 0.85 3/16/2004
7 | apples 0.5 3/17/2004
8 | oranges 0.75 3/17/2004
9 | apples 0.7 3/18/2004
10 | lemmons 0.6 3/18/2004
11 | oranges 0.85 3/19/2004
12 | apples 0.85 3/19/2004
13 | apples 0.5 3/20/2004
14 | oranges 0.75 3/20/2004
15 | apples 0.7 3/21/2004
16 | lemmons 0.6 3/22/2004
17 | oranges 0.85 3/23/2004
18 | apples 0.85 3/24/2004
19 | apples 0.5 3/25/2004
20 | oranges 0.75 3/26/2004
21 | apples 0.7 3/27/2004
22 | lemmons 0.6 3/28/2004
23 | oranges 0.85 3/29/2004
24 | apples 0.85 3/30/2004
[/tt]

formula in D4 =SUMPRODUCT((A1:A24=D1)*((C1:C24>=D2)*(C1:C24<=D3))*B1:B24)

Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884