SUMIFS formula help - multi-criteria settings not correct.

Occasional Visitor

I have a multi-sheet budget spreadsheet at work.

Screenshot 2022-08-29 152517.png

 

 

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!

 

 

1 Reply

@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.

Harun24HR_0-1661753197765.png