Trying to total the number of times a word occurs

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
 
P

Peo Sjoblom

COUNTIF(Range,"yes")

--

Regards,

Peo Sjoblom

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

pcrum73

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
 
H

hgrove

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.
Click to expand...

Maybe not easier for only 3 worksheets, but

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

Peo Sjoblom

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Page numbering with Merged Document and IF statements 1
Embed the same Excel multiple times 0
Finding the proper style field and the numbered paragraph value within the style 0
Is there a function to count how many times a cells value is used? 0
VBA word change keys 0
Trying to count based on multiple criteria & cell comparisons 4
sum total of numbers that are a certain color? 3
Paste to excel from word - auto format problem 1

Top