Sumproduct to count across sheets

  • Thread starter Thread starter jgorow
  • Start date Start date
J

jgorow

Some of you tried to help me with this before, and I have read man
suggestions from previous threads. However, for some reason I canno
write a correct function:

I have several sheets which are all designed the same. The first shee
is titled Survey and the last is End_Sheet. I want to count the numbe
of times "Yes" appears in cell C14 of each sheet.

I was given the following example before:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&F1:F6&"'!C6"),"Yes"))

So I wrote:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&Survey:End_Sheet&"'!C14"),"Yes"))

I have tried all sorts of manipulations thinking I wasn't understandin
the example, but all with no success.

I would appreciate any further help you might give this inexperience
user.

Joh
 
It doesn't work that way. You have to put a list with all the sheet names in
a range
not just the first and last sheet. That means if you have 14 sheets that you
want to check you would need
to use a range like F1:F14 in order like

F1 1st
F2 2nd
F3 3nd
F4 4th
and so on


--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Thanks, I finally understand that I need a list of sheet names to refe
to.

If I have many sheets and am adding sheets as I go, is there of way of
creating the list of sheet names without typing them all in?

For example, say my list is in F1:F6, and I name it "MySheets". I
there a procedure which will continue adding sheet names to my list, o
do I have to manually add to the list.

Thanks again.

Joh
 
Back
Top