Auto Word Count for Excel

[John Mo]

I want to display a total of the occurences of a
particular word entered into an Excel sheet, into a
specific cell. As the sheet is changed, and there are more
occurances of the word entered, i want a running total
displayed in this cell. Can anyone here help with this?
thanks
JMO

 

[steve]

John,

This sounds like a worksheet function question. But...
Type your word in cell A1 and put this formula in cell B1
=COUNTIF(A2:IV65536,A1)

This looks at all cells except for the first row.

Excel shortened it to
=COUNTIF(2:65536,A1)
because I set it to look at all columns and now it looks at all rows except
for the first row.

Try it out...

 

[Trevor Shuttleworth]

John

assuming the formula is in cell A1 and the word you are looking for is in
cell B1, you could use:

Cell A1 contains "=COUNTIF(A2:B65536,B1)+COUNTIF(C:IV,B1)" no quotes

or, if you want to include (count) the word you are looking for:

Cell A1 contains "=COUNTIF(A2:A65536,B1)+COUNTIF(B:IV,B1)" no quotes

Regards

Trevor

 

[JohnMo]

Thank you very much for both responses. I see this is a
function question now, but I was clueless before any
direction Thanks again for your responses, well done.

 

[steve]

John,

You're very welcome!

Be aware that you can build a worksheet function into your code
myvar = Worksheetfuntion.COUNTIF(Range("A2:IV65536"),Range"A1")

(watch for word wrap)