Indirect function with array

[tiredofit]

Can someone please explain what this formula is doing?

=SUM(COUNTIF(INDIRECT({"a1","b","c1"}),apples))

I thought apples was a named range in the workbook but I can't find it.


Thanks

 

[Ramballah]

From sheet a1, b and c1 it will count how many times the word apples is mentioned.
or something that direction xd or it could be columns, idk never use indirect

 

[Norie]

Have you looked in the Name Manager?

 

[tiredofit]

Have you looked in the Name Manager?

Yes.

I even wrote a quick sub to unhide all named ranges but couldn't find it.

 

[RoryA]

Unless the formula returns a #NAME error, it must be a defined name.

 

[tiredofit]

Unless the formula returns a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NAME]#NAME [/URL] error, it must be a defined name.

It doesn't return #NAME .

Open Excel and in any cell other than A1, B1 and C1, type this:

=SUM(COUNTIF(INDIRECT({"a1","b1","c1"}),apples))

it shows 0 in my Excel 2010, despite there is no named range called apples.

 

[Norie]

If I array enter that formula and then use Evaluate Formula on it this is what I get after the 2nd step.

SUM(COUNTIF({#VALUE !,#VALUE !,#VALUE !},#NAME?))

The next step shows this,

SUM({0,0,0})

and the final step this.

0

 

[tiredofit]

Thanks for the explanation.

 

[RoryA]

My mistake - I had forgotten that COUNTIF would treat the #NAME error as a text criterion rather than returning it as an error.