Using IF function in excel to search for text across cells and she

[Guest]

I am trying to use the IF function to search for a certain abbreviation
across a number of different cells across several sheets. I.e. to count the
number of cells containing this abbreviation.

I have been able to use this for one cell using :

IF(D5="RAB",1,0)

I.e. if D5 contains RAB count 1, otherwise count 0.

How would I reproduce this for a range of cells?
Using the same principle :

IF(D25="RAB",1,0)
However this gives me a result of 0 (when it should be 1)

Im not that experienced with excel so am I using the wrong formula or not
filling correctly, any suggestions?

 

[shail]

Hi Rup,

You must be looking for the COUNTIF function

=COUNTIF(D25,"RAB")

Hope this works for you.

Thanks

Shail

 

[Guest]

your formula will work if you enter it by pressing ctrl and shift and enter
together
if "rab" is in any one of those cells you will get
your formula will look like this
{=IF(D25="RAB",1,0)}
the curly brackets indicate an array formula which will come automatically
upon using the crl shift enter keys together you cant type the {} yourself

 

[shail]

Hi Paul,

That will definitely return 1, but Rup might want to count the number
of thimes "RAB" are appearing. Not just want to know it these are
there.

Shail

 

[Guest]

Thanks, yes this does work perfectly, although to get info from different
sheets i have been using =(COUNTIF(D25,"RAB")+COUNTIF(...."RAB")) which
seems to work well.

Is it possible to autocomplete a new formula to count for "PAR" without
having to manually change the formula? Its not important but would save some
time

Thanks again

Rup

 

[Pete_UK]

You can put SUM around it like so:

=SUM(IF(D25="RAB",1,0))

then commit with CTRL-SHIFT-ENTER to make it an array function.

Hope this helps.

Pete

 

[shail]

hi again Rup,

To count from different worksheets you need to prefix the range with
the name of the sheet. Say if your sheet name is "data" then the
formula will be

=COUNTIF(data!D25,"RAB")+COUNTIF(......)

For the second requirement, you need to tell Excel what to COUNT. For
this you should include "PAR" in your formula as above in place of
"RAB"

Or

For example, have a cell where you can enter "RAB" or "PAR" say at cell
D1

Then the formula will be changed to
=COUNTIF(data!D25,D1)

now when you enter "RAB" at D1 you will get the count of "RAB" and when
you enter "PAR" you will get the count of "PAR".


Thanks again

Shail

 

[Guest]

=SUMPRODUCT(--(D25="RAB"))