Forum Discussion
Subtotal function with Text Criterias
If so you need to add the helper column to the table like
=AGGREGATE(3,5,[@Color])which indicates if current row is filtered or not.
With that formula in L2 is
=SUMIFS(Table1[[Number]:[Number]], Table1[[Fruit]:[Fruit]],L$1, Table1[[Helper]:[Helper]],1)and drag it to the right.
If exclude Fruit filter the rest sum could be received by reference on table total
Hi SergeiBaklan thank you for your example. I would like to sum outside the table, and to sum filtered values only. However, I am not too sure how to integrate SUMIFS() with SUBTOTAL() function. How could I do that?
Say for example, on another page, I have table headers for different fruits. When I adjust the slicer, I would like for the row below to calculate the sum of each category basis the filters used.
If so you need to add the helper column to the table like
=AGGREGATE(3,5,[@Color])which indicates if current row is filtered or not.
With that formula in L2 is
=SUMIFS(Table1[[Number]:[Number]], Table1[[Fruit]:[Fruit]],L$1, Table1[[Helper]:[Helper]],1)and drag it to the right.
If exclude Fruit filter the rest sum could be received by reference on table total
SergeiBaklan huge thanks! I've been looking for this exact solution. Have a merry Christmas and new year.
Thank you and Happy Holidays!