Criteria range in SUMIFS array formula

问题:

=ARRAYFORMULA(IF(ROW(A:A)=1,"Spend",SUMIFS(Expenses!G:G,Expenses!D:D,A:A,Expenses!B:B,"Budget")))

Doesn't produce a result

If I change the criteria that was "A:A" to a certain field it works

=ARRAYFORMULA(IF(ROW(A:A)=1,"Spend",SUMIFS(Expenses!G:G,Expenses!D:D,"A1",Expenses!B:B,"Budget")))

If I use simple SUMIF the first version also works

=ARRAYFORMULA(IF(ROW(A:A)=1,"Spend",SUMIF(Expenses!D:D,A:A,Expenses!G:G)))

How come not as SUMIFS?

回答1:

could it be u need to use sumproduct() function? you probably need to know which Excel version you are in.

if before 2007 SUMIFS() won't work.

rr