Hmm. This gets complicated quickly . . . you sure you just don't want to take my word for it?
OK, I assume you know how COUNTIFS works, for the basic case.
=COUNTIFS(B:B,1)
This looks down column B and counts all the rows that have 1 in that row. Basic. Now if you want to count all the rows with 1 or 6, you could do this:
=COUNTIFS(B:B,1)+COUNTIFS(B:B,6)
Again, still pretty basic. Now to get a little fancier, we could rewrite that as:
=SUM(COUNTIFS(B:B,{1,6}))
This is now an array formula. Normally when you enter an array formula, you have to enter it using Control+Shift+Enter, which tells Excel that somewhere in the formula, where there's normally a single value, there is now a range of values that you want Excel to calculate all at once. However, if you hard-code the array, using the {}, that also tells Excel it's an array formula. Let's say that there are 4 cells with a 1, and 7 cells with a 6. Excel calculates the COUNTIFS twice, once with the 1, and once with the 6, and returns an array with the answers, like this {4,7}. Then the SUM adds those up giving 11. If you want to be able to change the {1,6} to {2,8}, you could put the numbers you want in Q1:R1, and write the formula like this:
=SUM(COUNTIFS(B:B,Q1:R1))
but now you have to use Control+Shift+Enter because you're not using the {}.
Now let's get a little fancier. Let's say we have this:
=SUM(COUNTIFS(B:B,{1,6},H:H,{"Correspondent","Retail"}))
Notice that I'm using commas in both array constants. I'm also ignoring the other columns for now. What do you think this works out as? When Excel sees 2 horizontal arrays (a comma separates items on the same row, a semicolon means go the the first item on the next row - so if you use commas, it's a horizontal array), it processes them both concurrently. Take 1 element from array1, and 1 element from array2, then the 2nd element from array1, and the second element from array2, giving us:
=SUM(COUNTIFS(B:B,1,H:H,"Correspondent"))+SUM(COUNTIFS(B:B,6,H:H,"Retail"))
which is not really what you want. It leaves out 2 combinations: 1,Retail and 6,Correspondent. (Also note, if you tried: =SUM(COUNTIFS(B:B,{1,6},H:H,{"Correspondent","Retail","Wholesale"})) you'd get an error, since the arrays are not the same size.)
So then how does the original formula work with 2 arrays? One is horizontal {1,6}, and the other is vertical {"Correspondent";"Retail";"Wholesale"}. When Excel sees that, it knows that it can't pairwise take elements from the arrays. It creates what's called a Cartesian Product, basically combining every element from the first array with every element from the second array. If you had those arrays in Q1:R1 and U1:U3, it would look something like:
| Q | R | S | T | U | V |
|---|
| 1 | 1 | 6 | Correspondent | | 1/Correspondent | 6/Correspondent |
| 2 | | | Retail | | 1/Retail | 6/Retail |
| 3 | | | Wholesale | | 1/Wholesale | 6/Wholesale |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3
So internally, Excel creates a 2X3 array, performs a COUNTIFS for each variation in it, then SUMs it.
And after all that, here's the simple answer to your question, no, NO version of SUM(COUNTIFS can have more than 2 arrays in it, because that would entail creating a 3-D array, and Excel array functions only handle 1-d or 2-d arrays.
When you have other columns with just 1 possibility, that's fine, since the size of the array is still 2X3, each element in the array just has more parts to it.
Sometimes you can get 3-D equivalency with some complicated constructions, but those would be very dependent on the situation.
Hope this clears things up a bit more, and I didn't just muddy the waters more.
