Use cell references using sum(sumifs) with array

[dcbanks]

I have the following formula:
=SUM(SUMIFS(TSB!$I$5:$I$91,TSB!$J$5:$J$91,{"MA","MI","EQ"},TSB!$U$5:TSB!$U$91,"=MAR"))
It works perfectly.
MA, MI and EQ are Cost Codes, there could be up to 6. MAR is the Department. There are over 50 rows, each a unique combination of Cost Codes and Departments.
I want to put the Cost Code values into Z1 - Z6 and the Department into Z7. There will be at least one Cost Code 99% of the time but never more than 6. Therefore there could be blanks. There will always only be a single Department.
Question 1: What is the exact syntax for referencing a cell in the array? I get confused about "&" and where my quotes go.
Question 2: Occasionally a Cost Code might not have been allocated. As I have it at the moment (hard-coded) it will never pick up these blanks. As it is highly likely that there will blank cells in Z2-Z6 returned, will it pick up the blanks in J5-J91?

Thanks, David (a newbie here and rapidly approaching age '3 score and 10').

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(SUMIFS(TSB!$I$5:$I$91,$Z$1:$Z$6,TSB!$U$5:TSB!$U$91,$Z7))

 

[dcbanks]

Try...

=SUMPRODUCT(SUMIFS(TSB!$I$5:$I$91,$Z$1:$Z$6,TSB!$U$5:TSB!$U$91,$Z7))

Thanks Aladin, worked perfectly. However, it gave an 'argument' error to begin with, an argument was left out, here is the amended version in case someone else has the same question:

=SUMPRODUCT(SUMIFS(TSB!$I$5:$I$91,TSB!$J$5:$J$91,Data!$J$2:$O$2,TSB!$U$5:$U$91,Data!$P$2))


David

 

[Aladin Akyurek]

You are welcome.