SUMIFS as array formula: I think this will be easy.

[terrarium]

I have a sumifs formula that reads criteria in each of 5 columns beside it. E14 is criteria that is common between the formulas buy a14-d14 will very.

=SUMIFS(mapdol,Bucket,E14,ExpType,A14)+SUMIFS(mapdol,Bucket,E14,ExpType,B14)+SUMIFS(mapdol,Bucket,E14,ExpType,C14)+SUMIFS(mapdol,Bucket,E14,ExpType,D14)


I tried an array formula like this:


{=SUMIFS(mapdol,Bucket,E14,ExpType,A14:D14}


But it only returns 0, as if it didn't read any criteria. When I switch back to the long style it works fine though.


Any ideas?

 

[Luke M]

I think you can do it using SUMPRODUCT instead:


=SUMPRODUCT((mapdol)*(Bucket=E14)*(ISNUMBER(MATCH(ExpType,A14:D14,0))))