Functions- Finding and counting specified text in cell range

[holliedavis]

I need to identify the correct function to achieve the following task:
Search specified column to find "22087", and count the occurences.

The problem I am having is that the cells within this column often contain
more than one numeric value in each cell. For example, contents of one
single cell may look like this: 22087, 22058, 22064. This cell identifies
the locations of all the jobs a candidate is willing to consider. If a
candidate is willing to consider placement in multiple cities/states, they
are all listed within the cell.
I need to know how many candidates are applying for each location #, and
CountIF only seems to work if the cells contain only one value. Help?

 

[Toppers]

Try:

=SUMPRODUCT(--ISNUMBER(SEARCH("22087",A1:B100)))

HTH

 

[Bernie Deitrick]

Hollie,

Use the array formula (entered uisng Ctrl-Shift-Enter)

=SUM(ISNUMBER(SEARCH(D1,A1:A100))*1)

where D1 contains the location code that you are interested in, and A1:A100 has your list of values.

HTH,
Bernie
MS Excel MVP

 

[holliedavis]

This formula works perfectly... Thank you! One small question, though... I
am searching range G3:G1000 on 4 worsheets for this information. How do I
write the formula to search the same range in multiple sheets?

My formula looks like this so far, but I need to also search sheets 2, 3,
and 4 and end up with a total number.

=SUMPRODUCT(--ISNUMBER(SEARCH("22948",'Sheet 1'!G3:G1003)))

Thanks!!!!

 

[Kevin Vaughn]

You SHOULD be able to just add a + sign to the end of that formula, copy the
formula (not including the = or the newly added +) and change sheet 1 to
sheet 2. Repeat this for sheets 3 and 4.

Without testing, it should look something like:
=SUMPRODUCT(--ISNUMBER(SEARCH("22948",'Sheet
1'!G3:G1003)))+SUMPRODUCT(--ISNUMBER(SEARCH("22948",'Sheet
2'!G3:G1003)))+SUMPRODUCT(--ISNUMBER(SEARCH("22948",'Sheet
3'!G3:G1003)))+SUMPRODUCT(--ISNUMBER(SEARCH("22948",'Sheet 4'!G3:G1003)))

There may be another way of doing this
Actually, I did test this a little by adding sheet 1 - sheet 4 into my
workbook and putting 22948 somewhere in column g of each. I got 4 (which was
correct in my instance.)

 

[Pinkgoldfish]

Hello,

I had the same issue as "holliedavis", where I am trying to count multiple
occurrences within single cells. However, my trouble is that for some reason
I am not getting the correct number! I can see that the correct answer is 7
occurrences of "F", but the formula tells me there are only 4 occurrences. I
must be missing something, because with count I'm trying to do it is correct.
I am using the formula:
=SUMPRODUCT(--ISNUMBER(SEARCH("F",P631)))

A sample cell might be "PPF", blank, or just "F"

Thanks!

 

[Toppers]

If there can be mulfiple occurences of "F" in ONE cell e.g cell contains "PPF
F PPF" i.e this counts as 3, then try:

=SUMPRODUCT(LEN(P631)-LEN(SUBSTITUTE(P631,"F","")))

If a cell only ever contains ONE "F" then your formula should work: it did
for me.

HTH

 

[Pinkgoldfish]

Thank you, works perfect! I'm sure I'll be posting more questions as I get
further into this, thanks for your help!

 

[art]

How would I be able to see how mwny times a certain word is in a cell, for
example in cell A1 is the text milk bread milk, how can I know that there is
2 times the word milk in the cell?