Count # of occurances within a range

[dacards]

I've got cells that containt "work log" information that can vary in length and content. I need a formula to look at a range of cells (or each specific cell) and count the number of occurances of a word.

For example, I want to count the number of occurances for "blue":

A1=(red blue red red)
A2=(blue yellow red blue)
A3=(yellow green red)
A4=(blue blue blue red)

The result should be: Total blue = 6

or
A1=1
A2=2
A3=0
A4=3

Any help is appreciated.

 

[firefytr]

If you had the value you wanted to count in say cell B2, you could use this formula and then SUM on it ...

=(LEN(A1)-LEN(SUBSTITUTE(A1,$B$1,"")))/LEN($B$1)

It will count the characters, hence the division by the number of chars at the end.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[AnotherHiggins]

Well, maybe my brain isn't working right today, because this is a little convoluted, but it is the first method that comes to mind – but it will work:

[tab][COLOR=blue white]=(len(a1)-len(substitute(a1,"blue","")))/4[/color]

The way it works is this:

Take the number of characters in a cell, subtract from that the number of characters in the cell without any occurrences of the word "blue". Because "blue" is four characters long, divide the result by four to get the number of times "blue" is in the cell.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Damn your nimble fingers, firefytr!
*shakes fists in the air*

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

LOL! My apologies John. I promise I'll wait an extra hour tomrrow morning before I drink my coffee. ;-)

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[dacards]

What if my data contains values that are of the same length? In the below example, "blue" and "gold" are both 4 characters - but I only want to count the number of times "blue" appears.

A1=(red blue red red gold)
A2=(blue yellow red blue)
A3=(yellow green red)
A4=(blue blue gold blue red)

THANKS AGAIN TO EVERYONE FOR YOUR HELP.

 

[AnotherHiggins]

You are only substituting the word BLUE, so nothing else will be counted.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

If it's going to change, I recommend a cell holding the value ot check and reference it as my first post shows.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[KenWright]

If you wanted to interrogate the range as opposed to a specific cell, then the following formula array entered (Use CTRL+SHIFT+ENTER) will do that:-

=(SUM(LEN(A1:A4))-SUM(LEN(SUBSTITUTE(A1:A4,"blue",""))))/4

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------