SUBTOTAL in auto-filterered table

[Stevenn]

Hi,

I've got a table with a lot of raw data which looks something like:

==
TypeID, Amount
--
1, 200
1, 300
1, 100
2, 400
2, 500
8, 300
==

and I want another table in another sheet in the same workbook with data like this:

==
TypeID, Amount
--
1, 600
2, 900
3, 0
4, 0
5, 0
6, 0
7, 0
8, 300
9, 0
==

How is this possible? In other words, how can I get the subtotal of a autofiltered list?

 

[T. Valko]

Hi,

I've got a table with a lot of raw data which looks something like:

==
TypeID, Amount
--
1, 200
1, 300
1, 100
2, 400
2, 500
8, 300
==

and I want another table in another sheet in the same workbook with data like this:

==
TypeID, Amount
--
1, 600
2, 900
3, 0
4, 0
5, 0
6, 0
7, 0
8, 300
9, 0
==

How is this possible? In other words, how can I get the subtotal of a autofiltered list?

Try this...

Filtered (or unfiltered) data on sheet1:

Book1
	A	B
5	1	200
6	1	300
7	1	100
8	2	400
9	2	500
10	8	300
Sheet1

Summary sheet:

Book1
	A	B
5	1	600
6	2	900
7	3	0
8	4	0
9	5	0
10	6	0
11	7	0
12	8	300
13	9	0
Summary

This formula entered in B5 and copied down:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Sheet1!B$5,ROW(Sheet1!B$5:B$20)-ROW(Sheet1!B$5),0)),--(Sheet1!A$5:A$20=A5))

 

[Stevenn]

It works Thank you. I thought I had to do some tricky VBA code.

I've tried to use it with tables, so I can use named columns, such as ROW(Table1) instead of Sheet1!B$5:B$20, but I will only get the result 0 no matter what I do. Is it not possible to use the code with the functions of tables?

 

[T. Valko]

It works Thank you. I thought I had to do some tricky VBA code.

I've tried to use it with tables, so I can use named columns, such as ROW(Table1) instead of Sheet1!B$5:B$20, but I will only get the result 0 no matter what I do. Is it not possible to use the code with the functions of tables?

This works OK for me:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(INDEX(Table1[Amount],1),ROW(Table1[Amount])-MIN(ROW(Table1[Amount])),0)),--(Table1[TypeID]=A5))

On a side note...

Do you actually like formulas using the structured referencing syntax? I absolutely loathe them!

 

[Stevenn]

And it works for me too

Thank you very much. If the table is filtered it wont show the 'correct' amount. Is there a way to 'keep' the data even if it's hidden because of the filter?

Yes, I definitely like structured referencing syntax. I think it makes the syntax way more readable

 

[T. Valko]

And it works for me too

Thank you very much. If the table is filtered it wont show the 'correct' amount. Is there a way to 'keep' the data even if it's hidden because of the filter?

In other words, you want the grand total whether the data is filtered or not? Actually, that's a whole lot easier!

=SUMIF(Table1[TypeID],A5,Table1[Amount])

Yes, I definitely like structured referencing syntax. I think it makes the syntax way more readable

Yeah, there's one in every crowd!

 

[Stevenn]

It's just perfect! Sorry I made the problem that more difficult

Thank you very much!

 

[T. Valko]

It's just perfect! Sorry I made the problem that more difficult

Thank you very much!

You're welcome. Thanks for the feedback!