Is it possible to nest filter inside of sumif?

Copper 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

 
 

unnamed.png

 

4 Replies

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 

 

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

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?

@papa_austin 

 

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