Is it possible to nest filter inside of sumif?

%3CLINGO-SUB%20id%3D%22lingo-sub-2578185%22%20slang%3D%22en-US%22%3EIs%20it%20possible%20to%20nest%20filter%20inside%20of%20sumif%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2578185%22%20slang%3D%22en-US%22%3E%3CUL%3E%3CLI%3EOffice%20365%20without%20Lamda%20functionality%26nbsp%3B%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI%20have%20a%20large%20data%20set%20(1000%2B%20rows%20with%20100%2B%20columns)%3C%2FP%3E%3CP%3EBelow%20screen%20shot%20is%20a%20scaled-down%20version%20of%20the%20data%20set%20but%20I%20think%20it%20serves%20a%20good%20example.%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EI%20have%20multiple%20dynamic%20filtering%20need%3B%20my%20real%20example%20has%203%20columns%20that%20could%20be%20filtered%20on%26nbsp%3B%3C%2FLI%3E%3CLI%3EUser%20can%20select%20what%20columns%20they%20want%20to%20see%20dynamically%20-%20up%20to%205%20columns%3C%2FLI%3E%3CLI%3EUsing%20Filter%20function%2C%20I%20can%20apply%20the%20right%20filters%20and%20pick%20the%20columns%20that%20I%20need%20to%20return%26nbsp%3B%3C%2FLI%3E%3C%2FOL%3E%3CP%3EThere%20is%20where%20my%20problem%20starts.%20I%20am%20left%20with%20a%20filtered%20table%20that%20has%20multiple%20values%20per%20year.%3C%2FP%3E%3CP%3EAnd%20i%20want%20to%20add%20them%20up%20for%20ever%20year%20(instead%20of%20getting%202%20values%20for%202021%2C%20I%20want%20to%20add%20them%20to%20a%20single%20value).%3C%2FP%3E%3CP%3EThis%20seems%20do-able%20but%20I%20am%20not%20sure%20how.%20Please%20help%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22unnamed.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297897i2495D9AD82E0980A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22unnamed.png%22%20alt%3D%22unnamed.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2578185%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2578404%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20to%20nest%20filter%20inside%20of%20sumif%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2578404%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1109705%22%20target%3D%22_blank%22%3E%40papa_austin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20providing%20a%20sample%20%2B%20expected%20result.%20Decomposed%20this%20could%20be%20something%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(%0A%20%20%20%20h%2C%20%20(Table1%5B%23Headers%5D%3DB24)%20%2B%20(Table1%5B%23Headers%5D%3DC24)%20%2B%20(Table1%5B%23Headers%5D%3D%22Year%22)%2C%0A%20%20%20%20f%2C%20%20FILTER(%20FILTER(Table1%2CTable1%5BText1%5D%3DB23)%2C%20h)%2C%0A%20%20%20%20y%2C%20%20INDEX(f%2C%2C1)%2C%0A%20%20%20%20s%2C%20%20SEQUENCE(%2CCOLUMNS(f)-1%2C2)%2C%0A%20%20%20%20v%2C%20%20INDEX(f%2CSEQUENCE(ROWS(f))%2Cs)%2C%0A%20%20%20%20t%2C%20%20MMULT(v%2C%20SEQUENCE(COLUMNS(v))%5E0)%2C%0A%20%20%20%20CHOOSE(%7B1%3B2%7D%2C%20y%2C%20t)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20is%20implemented%20in%20the%20attached%20file%3C%2FP%3E%3CP%3EI%20would%20highly%20recommend%20you%20refer%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fexcel-365-sum-dynamic-array-at-row-level%2Fm-p%2F2456557%22%20target%3D%22_blank%22%3EExcel%20365%20SUM%20dynamic%20array%20at%20row%20level%3C%2FA%3E%26nbsp%3Bwhere%20you'll%20find%20different%20approaches%20to%20this%20and%20other%20calcs%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2578407%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20to%20nest%20filter%20inside%20of%20sumif%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2578407%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1109705%22%20target%3D%22_blank%22%3E%40papa_austin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20understand%20correctly%2C%20I%20think%20this%20is%20one%20way%20you%20could%20do%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(IFERROR(NUMBERVALUE(Table1)%2C0)*MMULT((Table1%5BYear%5D%3DA38)*(Table1%5BText1%5D%3D%24B%2423%3A%24C%2423)%2C--(TRANSPOSE(COLUMN(%24B%2423%3A%24C%2423)%26gt%3B0)))*TRANSPOSE(MMULT(--(TRANSPOSE(Table1%5B%23Headers%5D)%3D%24B%2424%3A%24C%2424)%2C--(TRANSPOSE(COLUMN(%24B%2424%3A%24C%2424)%26gt%3B0)))))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2578964%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20to%20nest%20filter%20inside%20of%20sumif%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2578964%22%20slang%3D%22en-US%22%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EI%20do%20not%20have%20a%20version%20of%20Office%2065%20with%20Let%20function%20and%20I%20have%20no%20option%20to%20get%20it%20(Semi%20annual%20enterprise%20channel%20license)%3CBR%20%2F%3E%3CBR%20%2F%3EWhile%20this%20approach%20would%20work%20great%20if%20I%20did%20have%20let%2Flambda%20features%2C%20could%20you%20do%20it%20without%20them%3F%3C%2FLINGO-BODY%3E
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

 
 

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