Trying to total the number of times a word occurs

I am trying to count the number of times a certain word occurs in a
certain cell on 10 different worksheets.
=IF(C70,"yes", "no") is the function I use in the cell and on the last
worksheet I want to total the number of times "yes" is printed from the
IF statement. I want it done automatically b/c I need the value from
the total in different parts of the workbook.


---
Message posted from http://www.ExcelForum.com/

COUNTIF(Range,"yes")

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


"pcrum73 " wrote in message
news
I am trying to count the number of times a certain word occurs in a
certain cell on 10 different worksheets.
=IF(C70,"yes", "no") is the function I use in the cell and on the last
worksheet I want to total the number of times "yes" is printed from the
IF statement. I want it done automatically b/c I need the value from
the total in different parts of the workbook.


---
Message posted from http://www.ExcelForum.com/

Hi
try for one worksheet
=COUNTIF(C:C,"yes")

--
Regards
Frank Kabel
Frankfurt, Germany


I am trying to count the number of times a certain word occurs in a
certain cell on 10 different worksheets.
=IF(C70,"yes", "no") is the function I use in the cell and on the
last worksheet I want to total the number of times "yes" is printed
from the IF statement. I want it done automatically b/c I need the
value from the total in different parts of the workbook.


---
Message posted from http://www.ExcelForum.com/

to use the (range,"yes") it would look something like this

(SHEET1!A1 and SHEET2!A1 and SHEET3!A1),"yes"

I don't know how to seperate the cells so that I don't get an error, so
I have used COUNTIF(SHEET1!A1, "yes")+COUNTIF(SHEET2!A1,"yes").... It
works but I was hoping for a little easier way to do it.


---
Message posted from http://www.ExcelForum.com/

pcrum73 wrote...
...
I don't know how to seperate the cells so that I don't get an
error, so I have used COUNTIF(SHEET1!A1, "yes")
+COUNTIF(SHEET2!A1,"yes").... It works but I was hoping for
a little easier way to do it.

Maybe not easier for only 3 worksheets, but

=SUMPRODUCT(COUNTIF(INDIRECT("'"&
{"Sheet1";"Sheet2";"Sheet3"}&"'!"&CELL("Address",A 1))."yes"))


---
Message posted from http://www.ExcelForum.com/

If indeed the sheets are named sheet1 - sheet10 you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT ("1:10"))&"'!"&CELL("addre
ss",A1)),"yes"))

since I doubt that it is better to put all sheet names in let's say A1:A10
and name the range Lst, then use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Lst&"'!"&CELL("ad dress",A1)),"yes"))

the cell function is thre so you can copy down and change cell but if the
cell reference is fixed at A1 you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT ("1:3"))&"'!A1"),"yes"))

or

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Lst&"'!A1"),"yes" ))

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


"pcrum73 " wrote in message
news
to use the (range,"yes") it would look something like this

(SHEET1!A1 and SHEET2!A1 and SHEET3!A1),"yes"

I don't know how to seperate the cells so that I don't get an error, so
I have used COUNTIF(SHEET1!A1, "yes")+COUNTIF(SHEET2!A1,"yes").... It
works but I was hoping for a little easier way to do it.


---
Message posted from http://www.ExcelForum.com/