Using INDIRECT with quotation marks

**eightyfish** · 01-17-2019, 08:26 PM

Hi all, trying to figure this out but can't find a solution...

I'm trying to create the following formula using INDIRECT: =COUNTIF(A1,"A")

(The result of this formula is 1 as A1 contains the letter A)

I want to grab the letter A in the argument from cell A2.

This is the formula I've got: =COUNTIF(A1,INDIRECT("""&A2&"""))

So INDIRECT("""&A2&""") should generate the text "A" - but this does not work. The result of the formula is 0.

I have also tried a varient: =COUNTIF(A1,INDIRECT(CHAR(34)&A2&CHAR(34)))

Any idea what's going on? Images attached to help... THANK YOU

1.JPG
3.JPG
2.JPG

**benishiryo** · 01-17-2019, 08:39 PM

you might be trying to solve a bigger problem but you are not showing us the full picture. you can't use INDIRECT here as the cell in A2 does not look like a cell reference. how indirect works is more like you have the text "B10" inside cell A5. and B10 has the text "a". then:
=COUNTIF(A2,INDIRECT(A5))

but based on your eg, you could simply use:
=COUNTIF(A2,A2)

of course, like i said, you might be looking at a bigger picture we don't see.

**eightyfish** · 01-17-2019, 08:49 PM

Thanks for that - you're correct that this is part of a bigger problem (was trying to simplify...) here's the actual issue...

a.JPG

c.JPG

I want to count all cells that contain AUFCC in col A if col B contains a Y - but I want to create a smart formula that I can drag down for multiple strings beyond AUFCC... Maybe there is a simpler solution than using INDIRECT...

Cheers.

**Phuocam** · 01-17-2019, 09:05 PM

Do not using the INDIRECT() function

=COUNTIFS(B:B,"Y",A:A,"*"&C10&"*")

**eightyfish** · 01-17-2019, 09:08 PM

Thank you. Was making it overcomplicated.

**Glenn Kennedy** · 01-17-2019, 09:08 PM

Next time, please post a sheet, not a non-editable picture of one...

=COUNTIF(A:A,"*"&C3&"*")

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.