How do you subtotal a sum?

[rizzo93]

When I filter a table, I have this formula to tell me how many rows are being displayed as a result of the filtering:

SUBTOTAL(102,PeriTable[ID])

I now need to do something similar for another column, but in this case I am totaling the numbers contained in these cells. As I filter the table, I need this total to take into account the cells that are being filtered out.


How can I achieve this please?

 

[Scott T]

Function_num

Required. The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.​

from https://support.office.com/en-us/article/subtotal-function-7b027003-f060-4ade-9040-e478765b9939

You would need to find a different way to count. If you are able to post a sample of your data someone may be able to come up with something.

 

[rizzo93]

Sure, here's a basic example:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date

[/TD]
[TD]Product
[/TD]
[TD]Qty
[/TD]
[/TR]
[TR]
[TD]4/15/2019
[/TD]
[TD]apple

[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]4/15/2019
[/TD]
[TD]orange
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]4/15/2019
[/TD]
[TD]grape
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]4/15/2019
[/TD]
[TD]mango
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]4/15/2019
[/TD]
[TD]banana
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]2/17/2019
[/TD]
[TD]apple
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2/17/2019
[/TD]
[TD]orange
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2/17/2019
[/TD]
[TD]grape
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]2/17/2019
[/TD]
[TD]mango
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]2/17/2019

[/TD]
[TD]banana
[/TD]
[TD]2

[/TD]
[/TR]
</tbody>[/TABLE]


So we have 10 rows. The sum total comes to 38.

Using the formula I initially provided, it will yield a number of 10 rows. If I filter on just 4/15/2019, that formula will yield 5 rows. All is good to this point.

My goal is to have the sum total change along with the filter. So in this example above, it should show 17.

 

[Fluff]

You can use the same formula, but use 109 instead of 102

 

[rizzo93]

I'm cured. Thanks, Fluff!!!!

 

[Fluff]

You're welcome & thanks for the feedback