How to use multiple cells in countif formula

Question

I need to set the range for the countif formula to specific cell that are not a complete row or a complete column, e.g. A1, B3, D6. When definining these cells in a named range and applying the countif formula to the defined named range, the count is not performed.

Answer 1

This should work, replace the "X" with your count criteria

=SUM(COUNTIF(INDIRECT({"A1","B3","D6"}),"X"))

Answer 2

Alright, this will be a somewhat more extensive formula and expands on the fine answer given by @Jo.lass, but for whoever may find it usefull:

=SUMPRODUCT(COUNTIF(INDIRECT(TRIM(MID(SUBSTITUTE(CELL("adres",NamedRange),",",REPT(" ",100)),100*(ROW(INDIRECT("1:"&(LEN(CELL("adres",NamedRange))-LEN(SUBSTITUTE(CELL("adres",NamedRange),",",""))+1)))-1)+1,100))),"X"))

---

What does this do exactly:

It takes the adres of the NamedRange (change accordingly) and converts that into a string. In your case "$A1$,$B$3,$D$6".

Then we break this string up into an actual array of refernces to be used in INDIRECT just as the other answer does.

The advantage however is that, once the formula is in place, you don't have to come back to change references inside the actual formula, meaning: When your namedRange changes (e.g) shrinks/expands, the formula will adapt it's answer accordingly.

Answer 3

Just for FYI we can switch to SUMPRODUCT and avoid the Volatile INDIRECT().

=SUMPRODUCT(--(CHOOSE({1,2,3},A1,B3,D6)=1))

Volatile formula recalculate every time Excel Recalculates and too many can slow down the response time of excel.