Forum Discussion

Mmg_88's avatar
Mmg_88
Copper Contributor
May 11, 2023

Slicer Filter

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!

 

Excel
  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    May 11, 2023

    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.

    • Mmg_88's avatar
      Mmg_88
      Copper Contributor
      May 11, 2023

      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.

      • Martin_Weiss's avatar
        Martin_Weiss
        Bronze Contributor
        May 11, 2023

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

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

        Maybe this helps?

         

Share

Resources