counting text same cell multiple worksheets
 

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.

counting text same cell multiple worksheets
 

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.

counting text same cell multiple worksheets
 

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

counting text same cell multiple worksheets
 

Thanks Biff..I mentioned this way as i am not sure if the OP is familar...and
so thought to avoid any confusion..

If this post helps click Yes
---------------
Jacob Skaria


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

counting text same cell multiple worksheets
 

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.

counting text same cell multiple worksheets
 

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.

counting text same cell multiple worksheets
 

what if there are 1000 sheets

On each sheet, in the same cell, enter this formula:

=--(C6="yes")

You don't have to do this 1000 times, once for each sheet! Group the sheets
together and enter the formula on 1 sheet. When the sheets are grouped what
you do on 1 sheet will be done to every sheet that is included in the group.
Just make sure you ungroup the sheets after you enter the formula.

Then, to get your summary count...

Assuming you put that formula in cell A1 on each sheet:

=SUM(Sheet1:Sheet1000!A1)


--
Biff
Microsoft Excel MVP


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

counting text same cell multiple worksheets
 

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.

counting text same cell multiple worksheets
 

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.

counting text same cell multiple worksheets
 

I am trying to understand and apply this. How when entering =--(C6="yes")
being applied to all worksheets allow me to either type yes or no or select
yes or no from a dropdown list when the formula is within the cell. I
probably do not need to worry about grouping or ungrouping worksheets. I am
trying to make a Summary and Entity worksheet. After formatting the Entity
worksheet. I will copy it for other worksheets. At the start I do not know
the total number of Entity sheets that will be made. Initially I would like
to created a formula that will refer back to the summary sheet to add all of
the yes values from the Entity sheet.
"T. Valko" wrote:

what if there are 1000 sheets

On each sheet, in the same cell, enter this formula:

=--(C6="yes")

You don't have to do this 1000 times, once for each sheet! Group the sheets
together and enter the formula on 1 sheet. When the sheets are grouped what
you do on 1 sheet will be done to every sheet that is included in the group.
Just make sure you ungroup the sheets after you enter the formula.

Then, to get your summary count...

Assuming you put that formula in cell A1 on each sheet:

=SUM(Sheet1:Sheet1000!A1)


--
Biff
Microsoft Excel MVP


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

counting text same cell multiple worksheets
 

If you want to test C6 on all those sheets against the contents of another
cell then just replace"yes" with the cell reference:

Summary!A1 = Yes or No or whatever

=--(C6=Summary!A1)

That formula will return either a 1 or a 0.

If the number of sheets will vary because you add/delete sheets then create
a "sheet sandwich".

Insert a new sheet immediately to the left of the first sheet you want to
include and name this new sheet First. Insert a new sheet immediately to the
right of the last sheet you want to include and name this new sheet Last.

Then:

=SUM(First:Last!A1)

When you add new sheets to be included in the calculation just add them
between First and Last.


--
Biff
Microsoft Excel MVP


"Robin" wrote in message
...
I am trying to understand and apply this. How when entering =--(C6="yes")
being applied to all worksheets allow me to either type yes or no or
select
yes or no from a dropdown list when the formula is within the cell. I
probably do not need to worry about grouping or ungrouping worksheets. I
am
trying to make a Summary and Entity worksheet. After formatting the Entity
worksheet. I will copy it for other worksheets. At the start I do not know
the total number of Entity sheets that will be made. Initially I would
like
to created a formula that will refer back to the summary sheet to add all
of
the yes values from the Entity sheet.
"T. Valko" wrote:

what if there are 1000 sheets

On each sheet, in the same cell, enter this formula:

=--(C6="yes")

You don't have to do this 1000 times, once for each sheet! Group the
sheets
together and enter the formula on 1 sheet. When the sheets are grouped
what
you do on 1 sheet will be done to every sheet that is included in the
group.
Just make sure you ungroup the sheets after you enter the formula.

Then, to get your summary count...

Assuming you put that formula in cell A1 on each sheet:

=SUM(Sheet1:Sheet1000!A1)


--
Biff
Microsoft Excel MVP


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

counting text same cell multiple worksheets
 

SIR. I have 19 Worksheets in the same workbook, we'll call them ROUND 1, ROUND 2 and so on......in that same cell having differnet values (e.g. C6 of all work sheet). I want to add C6 cell of all worksheet in last work sheet named summary Please tell me the formula to import the data. I would be greatly appreciated.

counting text same cell multiple worksheets
 

On Sunday, August 16, 2009 at 1:32:39 PM UTC-5, Domenic wrote:
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

I have the same scenario as above, but my sheets are just numbered 1-250. Trying to figure out how to edit the formula.

Thanks!