Summing specific texts

[LostLady]

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!

[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

[Ray A]

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!
>
>
>
>
> --
> LostLady
> ------------------------------------------------------------------------
> LostLady's Profile: http://www.excelforum.com/member.php...o&userid=23447
> View this thread: http://www.excelforum.com/showthread...hreadid=377747
>
>

[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