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:

Date	Product	Qty
4/15/2019	apple	1
4/15/2019	orange	5
4/15/2019	grape	2
4/15/2019	mango	2
4/15/2019	banana	7
2/17/2019	apple	1
2/17/2019	orange	1
2/17/2019	grape	9
2/17/2019	mango	8
2/17/2019	banana	2

<tbody>
</tbody>

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