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

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...

--
sb
"John Mo" wrote in message
...
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

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


"John Mo" wrote in message
...
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

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.
-----Original Message-----
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...

--
sb
"John Mo" wrote in message
...
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



.

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)
--
sb
"JohnMo" wrote in message
...
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.
-----Original Message-----
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...

--
sb
"John Mo" wrote in message
...
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



.