Forum Discussion

papa_austin's avatar
papa_austin
Copper Contributor
Jul 23, 2021

Is it possible to nest filter inside of sumif?

  • Office 365 without Lamda functionality 

I have a large data set (1000+ rows with 100+ columns)

Below screen shot is a scaled-down version of the data set but I think it serves a good example. 

  1. I have multiple dynamic filtering need; my real example has 3 columns that could be filtered on 
  2. User can select what columns they want to see dynamically - up to 5 columns
  3. Using Filter function, I can apply the right filters and pick the columns that I need to return 

There is where my problem starts. I am left with a filtered table that has multiple values per year.

And i want to add them up for ever year (instead of getting 2 values for 2021, I want to add them to a single value).

This seems do-able but I am not sure how. Please help

 
 

 

 

excel
Formulas and Functions
office 365

4 Replies

Sort By
  • JMB17's avatar
    JMB17
    Bronze Contributor
    Jul 23, 2021

    papa_austin 

     

    If I understand correctly, I think this is one way you could do it. 

     

    =SUM(IFERROR(NUMBERVALUE(Table1),0)*MMULT((Table1[Year]=A38)*(Table1[Text1]=$B$23:$C$23),--(TRANSPOSE(COLUMN($B$23:$C$23)>0)))*TRANSPOSE(MMULT(--(TRANSPOSE(Table1[#Headers])=$B$24:$C$24),--(TRANSPOSE(COLUMN($B$24:$C$24)>0)))))

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jul 23, 2021

    Hi papa_austin 

     

    Thanks for providing a sample + expected result. Decomposed this could be something like:

     

    =LET(
    h,  (Table1[#Headers]=B24) + (Table1[#Headers]=C24) + (Table1[#Headers]="Year"),
    f,  FILTER( FILTER(Table1,Table1[Text1]=B23), h),
    y,  INDEX(f,,1),
    s,  SEQUENCE(,COLUMNS(f)-1,2),
    v,  INDEX(f,SEQUENCE(ROWS(f)),s),
    t,  MMULT(v, SEQUENCE(COLUMNS(v))^0),
    CHOOSE({1;2}, y, t)
)

    This is implemented in the attached file

    I would highly recommend you refer to Excel 365 SUM dynamic array at row level where you'll find different approaches to this and other calcs

     

    • papa_austin's avatar
      papa_austin
      Copper Contributor
      Jul 23, 2021
      Hello Lorenzo

      I do not have a version of Office 65 with Let function and I have no option to get it (Semi annual enterprise channel license)

      While this approach would work great if I did have let/lambda features, could you do it without them?

Resources