Summing specific texts

L

LostLady

Hi, please help me, I need assistance:

I am trying to find the total number of times a specific word has bee
entered in a column. I tried using LEN and SUBSTITUE but it seems lik
that only works if it's a single letter?

Here's the details - some one much smarter than I am will surely hav
an answer for me?

Word is *Toffee *
(and is written in A1)

The field is *D1 to D600 *(a text formatted column containing singl
words in each cell)

-I have had some success with the LEN and SUBSTITUTE formula when usin
a single letter but I have had to write out the whole field a
D1&D2&D3&D4&D5... etc, it wouldn't work using D1:D10) - I dont want t
write 1 to 600 with & between each entry - is there another way?-

The Total should be supplied in *B1* (in number format)

Look forward to hearing your response
Cheers
LOSTLADY!

:confused
 
S

swatsp0p

LostLady: We don't want you to be lost for too long. Use COUNTIF with
a 'wildcard' to find your search word in a range, as such:

In B1 place this formula:

=COUNTIF(D1:D600,"*TOFFEE*")
--- This will find all cases of ToFfeE in your range, such as "Toffee
Crisp", "Ground TOFFEE", "nuts toffee coated" will return 3

If you want to search for the contents of A1, then use:

=COUNTIF(D1:D600,"*"&A1&"*")

Does this work for you?

Bruce
 
R

Ron Coderre

If the word "Toffee" can appear more than once in a cell and you want to
know how many times Toffee appears in a range of cells

Ttry this:

=SUMPRODUCT(LEN(D1:D600)-LEN(SUBSTITUTE(UPPER(D1:D600),UPPER(A1),"")))/LEN(A1)

Example:
A1: Toffee and donuts
A2: Toffee is not Toffee-free.

Count of Toffee is 3.

Note: SUBSTITUTE is case sensitive, hence the UPPER function.

I hope that helps.

Ron
 
Top