Count number of * in a cell range

[steallan]

hi

i'm just trying to find a formula to count the number of occurances of a character (a ; to be precise) in a range of cells.

Im not after it to count the number of cells that contain a ; but rather the total number of them.

If there a formula in excel to do this? Can only find ones that count the cell, problem is I have some cells with the character in a few times and I want it to count it each time.

Im not after a one off count either, I need the formula to sit in the cell and update if the char is added in the range.

Any tips be very helpful thanks

ste

 

[kidwispa]

Try this...

=SUM(LEN(range)-LEN(SUBSTITUTE(range,";","")))

Change both instances of the word "range" above to the range you want to search (ie A1:B100)

Confirm with Ctrl,Shift + Enter.

 

[steallan]

I say good golly that works like a charm!

Thank you very much

 

[kidwispa]

I say good golly that works like a charm!

Thank you very much

No worries - I had no idea how to do it before you asked the question but found the answer elsewhere...

http://support.microsoft.com/kb/213889