CODEWITHSUNDEEP
excelexcel-formula
zszep
zszep

Reputation: 4458

How to use multiple cells in countif formula

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.

Upvotes: 0

Views: 300

Answers (3)

Scott Craner
Scott Craner

Reputation: 152450

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.

Upvotes: 1

JvdV
JvdV

Reputation: 75840

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.

Upvotes: 1

JLCH
JLCH

Reputation: 803

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

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

Upvotes: 3

Related Questions