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!

 

[onetap]

Also, do you not use isnumber(match on the last criteria because it is not an arrary, or just because it is the last criteria?

 

[Aladin Akyurek]

^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?

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

1. While "2/1/2017" is a date for us, it's text to Excel. "2/1/2017"+0 makes that text date a true date, i.e. a number.

2. ISNUMBER(MATCH(range,set,0)) means if an element of range is in set, it's ok. This gives us a multiple or test.

Also, do you not use isnumber(match on the last criteria because it is not an arrary, or just because it is the last criteria?

If set consists of a single value, we have an equality test, not a multiple or test, hence no isnumber/match test.

Hope this helps.

 

[onetap]

Very helpful, thank you!

 

[Aladin Akyurek]

Very helpful, thank you!

You are welcome.