Using INDIRECT as part of a COUNTIF when part of it is a text string

[cybastov]

Hi

I am guessing this is easy meat for most of you out there, I am new to using INDIRECT, I am still struggling to understand some of the functions, tried using the Excel help and this is still not working for me.

My formula is easy

COUNTIF(Details!C:C,"Incorrect code")

What I want is an INDIRECT to use B1 which has the words incorrect code in it, but I cannot get it to work, not sure if I am not using " in the right place, or if I should be using ', I really want to use INDIRECT as I have a lit of words, and want only 1 formula to count how many times those words appear in the sheet called Details.

Thanks in advance.

Stov

 

[Marcelo Branco]

If B1 is equal to Incorrect code you can simply use
=COUNTIF(Details!C:C,B1)

You don't have to use INDIRECT.

M.

 

[cybastov]

Thanks Marcelo, guess still more to be aware of

 

[tonwj]

I need help... I am trying to use countif with indirect but am confused... what I need is to countif a range say E6:e65...the range cells will contain initials like CC, TJ, LY and so forth... I want to use another cell like N5 to dictate which initials I am looking for..
for instance if N5 contains TJ then excel will look through the E6:E65 range to see how many times the initials are used.... can anyone hep me with this????
Or is there a better way to do this?
Thanks

 

[barry houdini]

Hello tonwj, welcome to MrExcel

It's better in most cases to start your own thread rather than piggyback on an old one (threads with zero replies will generally get more attention)

I'm not sure why you need INDIRECT, a basic COUNTIF like this seems to suffice

=COUNTIF(E6:E65,N5)