Sum Countifs Array with Multiples Criteria

[onetap]

Hello - I'm having hard time getting this formula to work. I figured out that the formula is not picking up the "C" in the array criteria for column H but not sure how to fix this. Any suggestions?

=SUM(COUNTIFS('ABC'!$CH:$CH, "8/1/2017",'ABC'!$H:$H, {"A","B","C"}, 'ABC'!$I:$I, {"D";"E"}, 'ABC'!$AC:$AC, {"F","G"}, 'ACM Cases'!$BB:$BB, "H"))

Thank you!

 

[FDibbins]

Not sure you can use {} the way you are.

Have you tried using SUMPRODUCT instead?

Also, avoid using full column ranges with ARRAYs or SP's

 

[onetap]

Is it because the array can't have 3 criteria? It works fine with two criteria within the brackets as well as with the full column selection so I don't think that is the issue.

I haven't tried sumproduct yet, I'm not as familiar with that as I am sum countifs

 

[Eric W]

Welcome to the board.

You can only use {} for an array twice in that kind of construction, once with a comma, once with a semicolon. If you try to use more, Excel will try to match elements from the arrays. In your case, Excel looks at A & F, then B & G, then quits because there's nothing to match with C. Notice you'll still never count A & G, or B & F. You'd have to rewrite your formula to something like:

=SUM(COUNTIFS('ABC'!$CH:$CH, "8/1/2017",'ABC'!$H:$H, {"A","B","C"}, 'ABC'!$I:$I, {"D";"E"}, 'ABC'!$AC:$AC,"F", 'ACM Cases'!$BB:$BB, "H"))+SUM(COUNTIFS('ABC'!$CH:$CH, "8/1/2017",'ABC'!$H:$H, {"A","B","C"}, 'ABC'!$I:$I, {"D";"E"}, 'ABC'!$AC:$AC,"G", 'ACM Cases'!$BB:$BB, "H"))

In case you're interested, or have trouble sleeping, I wrote up a long explanation of how this works here, in post #8 :

https://www.mrexcel.com/forum/excel-questions/1020655-countifs-multiply-criteria.html


And one other thing of interest: the xxxxIF functions can use whole column references since they recognize the last row used. But never use whole columns references with SUMPRODUCT or other array formulas. Do a search on SUMIF on this document from Microsoft:

https://msdn.microsoft.com/en-us/library/office/mt709003.aspx

 

[onetap]

Hmm interesting, thank you for the clear explanation.

That seems like it could make the formula long pretty quick. Is there a better way to go about this?

 

[FDibbins]

Maybe 1 way to keep the formula *relatively* short is to use a helper column (or 2) to do the heavy lifting for you. Something like this in Z, for instance...
and(or(H2="A",A2="B",A2="C"),or(I2="D",I2="E"),or(AC2="F",AC2="G"))

=SUM(COUNTIFS('ABC'!$CH:$CH, "8/1/2017",'ABC'!$H:$H, {"A","B","C"}, 'ABC'!$I:$I, {"D";"E"}, 'ABC'!$AC:$AC, {"F","G"}, 'ACM Cases'!$BB:$BB, "H"))
Then base the count on something like...
=COUNTIFS('ABC'!$CH:$CH, "8/1/2017", 'ACM Cases'!$BB:$BB, "H",Z:Z,TRUE)

 

[onetap]

Oh ok, I see what you're saying with the helper column, that seems like a feasible option although I would really like to try to avoid adding another column to the data set I'm working with.

Would this be easier with sumproduct/how would I structure the formula if that is possible?

Really appreciate your help!

 

[FDibbins]

based on your need to test 3 different cells for at least 2 (or 3) criteria each, I think you would be better served using the helper. That will also have the added benefit of allowing you to more easily adapt the helper if your requirements change. Also, if you really have a lot of data, you can start to copy/paste values to formulas whose answers will no longer change - which could also speed things up a bit

 

[Aladin Akyurek]

Hello - I'm having hard time getting this formula to work. I figured out that the formula is not picking up the "C" in the array criteria for column H but not sure how to fix this. Any suggestions?

=SUM(COUNTIFS('ABC'!$CH:$CH, "8/1/2017",'ABC'!$H:$H, {"A","B","C"}, 'ABC'!$I:$I, {"D";"E"}, 'ABC'!$AC:$AC, {"F","G"}, 'ACM Cases'!$BB:$BB, "H"))

Thank you!

Try...

=SUMPRODUCT(--(ABC!$CH$2:$CH$2000="8/1/2017"+0),--ISNUMBER(MATCH(ABC!$H$2:$H$2000,{"A","B","C"},0)),--ISNUMBER(MATCH(ABC!$I$2:$I$2000,{"D","E"},0)),--ISNUMBER(MATCH(ABC!$AC$2:$AC$2000,{"F","G"},0)),--(ABC!$BB$2:$BB$2000="H"))

It's better to avoid whole column references in this type of array-processing formulas for reasons of efficiency.

 

[onetap]

^this worked perfectly, much appreciated! Would someone be able to break down the formula for me?

I'm not following why the isnumber(match is followed by the first criteria for sumproduct. Also, what function does the +0 serve?