Share via

Excel - COUNTIF with INDIRECT works, however COUNTIF with INDIRECT with ADDRESS fails

Anonymous
2022-06-28T09:52:44+00:00

Hi

We ran into an issue with COUNTIF.

COUNTIF using INDIRECT works perfectly, howevere if the INDIRECT itself uses the ADDRESS function, the COUNTIF will fail (returns always 0).

We provide hereunder a very simplified example to showcase tha failure/bug.

This does not represent our data. We do not need a workaround for the moment, only at least a confirmation this is a bug for COUNTIF(INDIRECT(ADDRESS))).

Hopefully someone can confirm this is a bug or if we applied this the wrong way, an explanation how to use the combination COUNTIF(INDIRECT(ADDRESS))).

Kind regards

Luc

A B C D E F
SIMPLIFIED EXAMPLE to show issue with countif using INDIRECT and ADDRESS functions
name available result
a x explicit literal range $B:$B
b x
c x FORMULA result
d =COUNTIF($B:$B,"x") 3 works
e =COUNTIF(INDIRECT(E4),"x") 3 works
f =COUNTIF(INDIRECT(ADDRESS(4,5)),"x") 0 does not work
=ADDRESS(4,5) $E$4 works
=INDIRECT(ADDRESS(4,5)) $B:$B works
'=COUNTIF(INDIRECT(ADDRESS(4,5)),"x") 0 does not work

Microsoft 365 and Office | Excel | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2022-06-28T10:12:51+00:00

Try

=COUNTIF(INDIRECT(INDIRECT(ADDRESS(4,5))),"x")

Note:

As the help says, INDIRECT "returns the reference specified by a text string. " The text string should result in a RANGE ADDRESS.

So (INDIRECT(E4) will give you a REFERENCE to range $B:$B as E4 contains string $B:$B

INDIRECT(ADDRESS(4,5)) will give you a REFERENCE to range E4 as ADDRESS(4,5) evaluates to E4 and since E4 contains $B:$B, the final result will be the string $B:$B.

To get a REFERENCE to range $B:$B, you have to again wrap it in INDIRECT.

Hope this clarifies.

Was this answer helpful?

2 people found this answer helpful.
0 comments

Answer accepted by question author

Rory Archibald 18,885 Reputation points Volunteer Moderator
2022-06-28T10:14:27+00:00

You need another INDIRECT.

=COUNTIF(INDIRECT(INDIRECT(ADDRESS(4,5))),"x")

INDIRECT(ADDRESS(4,5))

is the same as INDIRECT("E4") - i.e. a reference to E4. E4 does not contain "x" therefore the result is 0. It is not the same as INDIRECT(E4) which is a reference to whatever range is specified in cell E4.

Was this answer helpful?

1 person found this answer helpful.
0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest