In which format are your sheets named? Are they in this format
'Entity1' (no space between Entity and 1)? Or are they in this format
'Entity 1' (a space between Entity and 1)? If the latter, try
=SUMPRODUCT(COUNTIF(INDIRECT("'Entity
"&ROW(INDIRECT("1:1000"))&"'!C6"),"Yes"))
If the former, replace
"'Entity "
with
"'Entity"
--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions
In article ,
Robin wrote:
Entity 1, Entity2,....... the total # number of sheets may or may not be pre
determined
"Domenic" wrote:
How are your sheets named?
--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions
In article ,
Robin wrote:
Thank you both, but what if there are 1000 sheets
Sincerely,
robin
"T. Valko" wrote:
=SUMPRODUCT(COUNTIF(INDIRECT("'"& {"sheet1","sheet2"} &"'!C6"),"Yes"))
=SUMPRODUCT(COUNTIF(INDIRECT("'sheet"&{1,2}&"'!C6" ),"Yes"))
--
Biff
Microsoft Excel MVP
"Jacob Skaria" wrote in message
...
Yes you can; and the answer for both the questions are same..Make
sure the
entries from the list are 'Yes' with out any spaces after..
The below will count cell c6 for Sheet1 and Sheet2. You can add more
sheets
to the array like {"sheet1","sheet2","sheet3","sheet4"}
=SUMPRODUCT(COUNTIF(INDIRECT("'"& {"sheet1","sheet2"}
&"'!C6"),"Yes"))
OR
you can type in the sheet names to a range of cells say from A1:A5 of
the
current sheet. Make sure all 5 cells contain a valid sheet name
=SUMPRODUCT(COUNTIF(INDIRECT("'"& A1:A5 &"'!C6"),"Yes"))
If this post helps click Yes
---------------
Jacob Skaria
"Robin" wrote:
1st question: Is there a way to add the text say cell C6 across
multiple
worksheets?
Ex. Either yes or no will be typed in cell C6 in multiple
worksheets. Say
there are 8 worksheets and 5 say yes and 3 say no? I would like to
determine
the number of cells that say yes without going back to count.
Also
2nd Question: Is there a way to add the text to cell C6 across
multiple
worksheets if
there were a datavalidated list with a selection of yes or no with
there
being 5 say yes and 3 say no? I would like to determine the number
of
cells
that say yes without going back to count.