Rules for Multiple Criteria SUMIFS/COUNTIFS and Arrays

Dear Forum,

In my new role I am analysing data a lot and the COUNTIF and SUMIF functions are my new best friends.

I am trying to understand the general principle of how to (reliably) build multiple criteria SUMIFS and COUNTIFS formulae, working on multiple criteria columns, using arrays. I thought as long as you didn't create an array structure that went beyond '2 dimensions' it would work, and generally they do. I have been playing around and I list out the results at the bottom of the thread (I don't have permission to attach files).

What is baffling me is why the COUNTIFS formula below does work, but the SUMIFS formula doesn't, but they have the same 3:1:3 array structure:

=SUM(COUNTIFS(B:B,{"x","y","z"},C:C,"d",D:D,{"7";"5";"8"}))

=SUM(SUMIFS(D:D,A:A,{"b","c","d"},B:B,"x",C:C,{"d","g","h"}))

The formulae are working on the following table:

colA colB colC colD
b x d 6
b x d 7
c x d 7
d x d 5
e x d 7
f y e 7
g x e 8

Experiment Results:

THESE WORK Array Structure
=SUM(COUNTIFS(B:B,{"x","y"},C:C,"d",D:D,"7")) 2:1:1
=SUM(COUNTIFS(B:B,{"x","y"},C:C,"d",D:D,{"7";"5"})) 2:1:2
=SUM(COUNTIFS(B:B,{"x","y","z"},C:C,"d",D:D,{"7";"5";"8"})) 3:1:3 **
=SUM(SUMIFS(D:D,A:A,"b",B:B,{"x","y"},C:C,"d")) 1:2:1
=SUM(SUMIFS(D:D,A:A,{"b","c"},B:B,{"x"},C:C,{"d","g"})) 2:1:2
=SUM(SUMIFS(D:D,A:A,{"b","c","d"},B:B,"x",C:C,"d")) 3:1:1
=SUM(SUMIFS(D:D,A:A,{"b","c"},B:B,{"x","y"},C:C,"d")) 2:2:1
=SUM(SUMIFS(D:D,A:A,{"b","c","d","e"},B:B,"x",C:C,"d")) 4:1:1
=SUM(SUMIFS(D:D,A:A,{"b","c"},B:B,"x",C:C,"d")) 2:1:1

THESE DON'T
=COUNTIFS(B:B,{"x","y"},C:C,"d",D:D,"7") doesn’t have sum
=SUM(COUNTIFS(B:B,{"x","z"},C:C,"d",D:D,{"7";"5";"8"})) 2:1:3
=SUM(SUMIFS(D:D,A:A,{"b","c"},B:B,{"x","y"},C:C,{"d","g"})) 3:2:2
=SUM(SUMIFS(D:D,A:A,{"b","c","d"},B:B,"x",C:C,{"d","g"})) 3:1:2
=SUM(SUMIFS(D:D,A:A,{"b","c","d"},B:B,"x",C:C,{"d","g","h"})) 3:1:3 **
=SUM(SUMIFS(D:D,A:A,{"b","c","d"},B:B,{"x","y","z"},C:C,{"d","g","h"})) 3:3:3
=SUM(SUMIFS(D:D,A:A,{"b","c"},B:B,{"x","y"},C:C,{"d","g"})) 2:2:2

Chris

Thanks. That helped!