Trying to total the number of times a word occurs

  • Thread starter Thread starter pcrum73
  • Start date Start date
P

pcrum73

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

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
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, s
I have used COUNTIF(SHEET1!A1, "yes")+COUNTIF(SHEET2!A1,"yes").... I
works but I was hoping for a little easier way to do it
 
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("address",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)
 
Back
Top