Forum Discussion

priddlel's avatar
priddlel
Copper Contributor
Aug 29, 2022

SUMIFS formula help - multi-criteria settings not correct.

I have a multi-sheet budget spreadsheet at work.

 

 

I am trying to total the spend for each department based on a selection of account codes.i.e:

  • total spend of all "Marketing" codes (yellow) used by 2176

  • total of all "sales" codes (green) used by 2977

etc etc.. and so on for the the boxes.

I have been using a SUMIFS function, but it is not giving back the correct results.
=SUMIFS(C:C,A:A,I3,B:B,F2:F11)

but I get =0 when a manual calculation results in $59

Any help would be appreciated!

 

 

excel
Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Aug 29, 2022

    priddlel You need to wrap SUMIFS() with SUM() function. Try-

    =SUM(SUMIFS(C2:C13,B2:B13,F2:F4,A2:A13,E2))

    Otherwise you can use SUMPRODUCT() like

    =SUMPRODUCT((C2:C13)*(ISNUMBER(MATCH(B2:B13,F2:F4,0)))*(A2:A13=E2))

    You can use FILTER() function if you have Microsoft 365. See the attached file.

     

Resources