Using Countif across multiple sheets

[Guy Hoffman]

Thanks Peo. Thats helpful.

Here is the last part of my probelm. I happen to know the sheetname
for the first (Tom) and last (Jane) sheets for which I want to sum up
all non-zero values in cell A1. I want to write a formula on a sheet
named "Summary" using VBA code in cell A1 (I'm thinking something like
Worksheet("Summary").Range("A1").FormulaArray = "....."). I then plan
on using the VBA code to copy cell A1 on Summary down for another 50
rows. This piece I have figured out. Its the "...." above that I have
been trying to resolve.

Any last comments or thoughts.

Best Regards,

Guy

 

[bfriesen]

I've read through all of these messages, tried a few of the suggestions
but can't find anything that works for me.

I have several sheets, one of which contains a list of ALL student
(Let's call it "Sheet1"). All the other sheets are for particula
classes and contain lists of the students registered in those classes.

My goal is to show on Sheet1 into how many classes each student i
registered.

I started by creating a 3-D Reference to Column A in all of the clas
sheets. Column A contains the student IDs. My Named Range (CLASSLISTS
looks something like this: ='Sheet2:Sheet8!$A:$A.

I tried to use this in a CountIf function that I placed in Sheet1. Th
formula looked like this: =COUNTIF(ClassLists,A3) In this case, A
Refers to the Student IDs contained in Column A of Sheet 1.

Well, as someone stated in an earlier message, it seems as though yo
can't use CountIf across worksheets. I end up getting the #VALUE
error.

I am able to get the values I want if I use CountIf, several times
inside a nested If. However, this ends up being a very long formul
would need to be changed whenever I add sheets for additional classes.

My brain is going numb trying to figure out a way around this, but
may have to settle for the long, long, high maintenance formula. An
assistance would be greatly appreciated.

Thanks, in advance

 

[Peo Sjoblom]

One way

http://tinyurl.com/3clz2

 

[Guy Hoffman]

Here is the answer that I finally ended up with for those that ma
experience a similar problem:

1. creat a list of sheets in the workbook that are in need o
utilizing a countif function

2. create a name for the list of sheet like "SheetList"

3. use the following formula:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!"&CELL("address",A1)),"<>0"))

This formula will count the number of cells on all sheets included i
the named range in cell A1 that are not zero.

Thanks for all the posts

 

[bfriesen]

Well, that doesn't work for me. I only get a #VALUE! error.

 

[Peo Sjoblom]

Thea is because you apply it incorrectly. Do you have all sheet names in a
list, are all quotes, apostrophes, etc in place?
Trust me, it works..

 

[bfriesen]

I have created a named range called SheetList, which looks like this
=Sheet2:Sheet8!$A:$A

Then, I cut and pasted your formula exactly as it is (see my past
below) into a cell in Sheet1 and I still get a #Value! error.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!"&CELL("address",A1)),"<>0"))

I seriously don't know what I'm doing wrong. I even tested this in
completely new workbook, with no data, just to be sure I didn't hav
any strangeness going on

 

[Peo Sjoblom]

No.. You have to physically (either by typing or by using a macro) create a
list with the sheet names, all of them
so if they are named after your students you need to put their names in the
list, so if you have 20 names you create
a list by selecting the list (for example A1:A20) and putting their names
there. Name it SheetList and use your formula and it will count values in A1
that are not zero.. You cannot use a 3D range, that is why this is a
workaround. It would be nice if it was possible

--

Regards,

Peo Sjoblom

 

[bfriesen]

Ah, ok. I can see now that it will work if I know exactly which cell t
look in (A1, in the example). Unfortunately, I need to look in th
entire A Column, and ... l guess that qualifies as a 3D reference
because it doesn't work to replace "A1" with a range

 

[Harlan Grove]

Ah, ok. I can see now that it will work if I know exactly which cell to
look in (A1, in the example). Unfortunately, I need to look in the
entire A Column, and ... l guess that qualifies as a 3D reference,
because it doesn't work to replace "A1" with a range.

Do you really mean the *ENTIRE* column A? From row 1 all the way through row
65536? If so, break up the conditional summing between rows 1 to 32768 and rows
32769 to 65536. If not, and there were, say, header text in row 1, you should
have no problem referencing A2:A65536.