Summing specific texts
 

Hi, please help me, I need assistance:

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

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

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

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

-I have had some success with the LEN and SUBSTITUTE formula when using
a single letter but I have had to write out the whole field as
D1&D2&D3&D4&D5... etc, it wouldn't work using D1:D10) - I dont want to
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:


--
LostLady
------------------------------------------------------------------------
LostLady's Profile: http://www.excelforum.com/member.php...o&userid=23447
View this thread: http://www.excelforum.com/showthread...hreadid=377747

In cell b1 format to number then use =countif(d1:d600,"toffee")

"LostLady" wrote:


Hi, please help me, I need assistance:

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

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

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

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

-I have had some success with the LEN and SUBSTITUTE formula when using
a single letter but I have had to write out the whole field as
D1&D2&D3&D4&D5... etc, it wouldn't work using D1:D10) - I dont want to
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:


--
LostLady
------------------------------------------------------------------------
LostLady's Profile: http://www.excelforum.com/member.php...o&userid=23447
View this thread: http://www.excelforum.com/showthread...hreadid=377747

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=377747

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=377747