How to subtotal filtered and unfiltered cells?

[288enzo]

Hi all

I'm trying to do something that I'm starting to think can't be done.

I'm trying to filter Table1 by category and update the subtotals above. The subtotal for the specific filtered category works as it should using subtotal 109, however what I can't figure out is how to subtotal multiple categories regardless if any category is actually filtered. One note is my table is ever growing, so I wouldn't be able to lock in a specific range.

Thank you

QQQ (1) (version 1).xlsb
	A	B
1	Subtotal Red & Green
2	Filtered Subtotal	51
3
4	Category	Amount
5	Green	4
6	Red	6
7	Blue	5
8	Green	8
9	Blue	3
10	Blue	7
11	Red	9
12	Green	2
13	Blue	5
14	Green	2
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUBTOTAL(109,Table1[Amount])

 

[Saba Sabaratnam]

I am not sure if I understand your requirement 100%. However, if you want Excel to ignore any filter that you have applied to your data and total everything, use SUM formula. On the other hand, if you want to add only visible rows, use SUBTOTAL formula which will ignore hidden rows.

Kind regards

Saba

 

[288enzo]

I am not sure if I understand your requirement 100%. However, if you want Excel to ignore any filter that you have applied to your data and total everything, use SUM formula. On the other hand, if you want to add only visible rows, use SUBTOTAL formula which will ignore hidden rows.

Kind regards

Saba

I'm looking for something in the middle. I want the filtered results to show the subtotal, this works. But at the same time I'd also like to always show the total of the Red and Green values. I started to think I could have a separate sheet that would list all Red and Green values using INDEX (just started reading into it) then the total could be pulled from that second sheet. I hope that helps. Thank you.

 

[Saba Sabaratnam]

you can use sumifs and sum as demonstrated below.

Enter the categories you want to total irrespective of these rows are filtered or not in D12 to D13.


Enter the following formula in B12

=SUM(SUMIFS($B$2:$B$9,$A$2:$A$9,D12:D13))

you can see the result below.

Kind regards

Saba

 

[288enzo]

Hi Saba - thank you for the help, but when I tried what you did I get zero. I'm not sure what I'm doing that's different. Could it be because my data is in a table? I tried to swap out the cell references with the table references, but that didn't work either. Thanks.

QQQ (1) (version 1).xlsb
	A	B
1	Filtered Subtotal	51
2
3	Category	Amount
4	Green	4
5	Red	6
6	Blue	5
7	Green	8
8	Blue	3
9	Blue	7
10	Red	9
11	Green	2
12	Blue	5
13	Green	2
14
15	Subtotal for:	0
16	Green
17	Red
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUBTOTAL(109,Table1[Amount])
B15	B15	=SUM(SUMIFS($B$4:$B$13,$A$4:$A$13,A16:A17))

 

[Saba Sabaratnam]

Hi

I used 365 to solve this problem. I think that you have different version of Excel.

if so, please enter your formula as array by pressing Shift + Control + Enter.

=SUM(SUMIFS($B$4:$B$13,$A$4:$A$13,A16:A17))

it should work as I tested using excel 2010

 

[288enzo]

Hi

I used 365 to solve this problem. I think that you have different version of Excel.

if so, please enter your formula as array by pressing Shift + Control + Enter.

=SUM(SUMIFS($B$4:$B$13,$A$4:$A$13,A16:A17))

it should work as I tested using excel 2010

That was the trick, thank you very much.