Sumproduct when 1 array has multiple criteria

[mark.c]

Hello

I would like to know if there is a work around to the following issue.

I have attached a spreadsheet of a short list for reference

I would like to use sumproduct (preferably over sumifs) to add the expenses for various departments

I am currently using the following formula
=SUMPRODUCT((GL=$T$5)*(DEPT=$T$2)*(DATE=U$4)*BUDGET)+SUMPRODUCT((GL=$T$5)*(DEPT=$T$3)*(DATE=U$4)*BUDGET)

I would like to eliminate the second part of the formula +SUMPRODUCT((GL=$T$5)*(DEPT=$T$3)*(DATE=U$4)*BUDGET) and include many departments in the DEPT Array.

(In my original list I have over 20 departments and cannot remove the DEPT ARRAY because it ties into other issues in my spreadsheet)

Any help is greatly appreciated
Thanks

[AliGW]

Welcome to the forum!

I think you mean this:

=SUMPRODUCT((GL=$T$5)*(DATE=U$4)*BUDGET)

[mark.c]

Thank you AliGW

But i need to include the departments into the formula

I would need it to look something like the following

=sumproduct(GL=$T$5)*(DEPT=TICKETING & HOLDING)*(DATE=$U4)*(BUDGET)

I need to combine the TICKETING & HOLDING into the DEPT array

Thx

[AliGW]

You could try this:

=SUMPRODUCT((GL=$T$5)*((DEPT=$T$3)+(DEPT=$T$2))*(DATE=U$4)*BUDGET)

[mark.c]

AliGW

You are a genius

Thank you so much

[AliGW]

No, I'm really not a genius - just someone who's curious and likes learning.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.