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?

 

Mmg_88_0-1683802352299.png

 

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")

e.g.

=SUBTOTAL(9, Table1[Total])

 

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

@Martin_Weiss 

 

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

=AGGREGATE(3,5,[@category])

Get_Slicer_Value1.png

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

=INDEX(Table1[category],MATCH(1,Table1[visible?],0))

Get_Slicer_Value2.png

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!!!