Is it possible to nest filter inside of sumif?

Occasional Contributor
  • 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




4 Replies

Hi @papa_austin 


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


    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




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



Hello @L z.

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?



With an Helper sheet (can be hidden) then. See attached file where I named your 3 inputs/parameters + the dynamic arrays that sit in the helper sheet