Slicer Filter

Copper Contributor

Hi all,

I have done a Slicer in excel, and now I would like to use SUMIF function, where the criteria is the number that I have filtered in the Slicer.

Do you know if it is possible to do that?




Thanks for your help!


4 Replies

Hi @Mmg_88 


if I don't get it wrong, you want to use the SUMIF function to sum the filtered values in the same table where the slicer gets applied.

For this, you do not need SUMIF, you can use instead the SUBTOTAL-function:


=SUBTOTAL(9, "column you want to sum")


=SUBTOTAL(9, Table1[Total])


It will sum only the visible values, which are the ones that you selected with your slicer.



No... I tryied, but I also need to add a condition that I can't (or I don't know how) use in SUBTOTAL function, that's why I want to use sumif.

Hi @Mmg_88 


ok. Then I have a different suggestion.

You would need to add a helper column to your data table, where you put an AGGREGATE formula. This formula returns a 1 if the respective line is visible or a 0 if it is not visible (e.g. due to a filter). Here is an example.


The formula in my helper column D is



And in C1 the INDEX/MATCH formula gets always the first visible value from the category column:



Maybe this helps?


Actually it helps. This function didn't work (in my Excel at least it didn't work exactly like that, maybe because it is in spanish and "agregar" was not exactly the same), but thanks of your function description I could find the function that worked in my file [SUBTOTAL(103,a2)].

Many many thanks!!!