Using Countif across multiple sheets

[Guy Hoffman]

I am attempting to count the number of non-zero cells in a rang
spanning multiple sheets.

For example, I have the following:

On Sheet1: A1 = 5
On Sheet2: A1 = 6

The formula on Sheet 3 is:

=COUNTIF(Sheet1:Sheet2!A1,">0")

It returns the #Value error.

Thanks.

G

 

[shades]

I don't think you can use COUNTIF across sheets. Here is the
alternative:

=COUNTIF(Sheet2!A1,">0")+COUNTIF(Sheet3!A1,">0")

 

[Guy Hoffman]

Thanks but the numbers of sheets I have is over 180. My example was
illustrative.

What I am actually attempting to do is determine the average of the
numbers in the multiple-sheet range, excluding zeros. So I am
considering using the countif and the sum function (which works fine
across multiple sheets) together.

Any other thoughts?

 

[Peo Sjoblom]

One way

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&{1,2}&"'!"&CELL("address",A1)),">0"))

or put all the sheet names in a range and call it MySheets

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

 

[shades]

Or another way. Label two sheets, "First" and "Last" (without quotes).

Then put all your sheets in between them. Then use this formula:

=SUM(First:Last!$A$1)/COUNT(First:Last!$A$1,">0")

 

[Peo Sjoblom]

Did you try that?

 

[Guy Hoffman]

*One way

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&{1,2}&"'!"&CELL("address",A1)),">0"))

or put all the sheet names in a range and call it MySheets

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

--

Regards,

Peo Sjoblom


message

Thanks. That works. What if the sheets are simply named "XYZ" an
"ABC"

 

[shades]

By using my method (First and LAst worksheets), it won't matter what the
sheets are labeled.

 

[Guy Hoffman]

Shades,

The count portion of the formula simply returns the number of sheet
between First and Last without regard to the value in cell A1

 

[shades]

I was trying to combine the two steps into one based on this:

What I am actually attempting to do is determine the average of the
numbers in the multiple-sheet range, excluding zeros. So I am
considering using the countif and the sum function (which works fine
across multiple sheets) together.

So my formula will give the average of all the cells, but do so based
on the number of cells greater than 0. The count portion returns how
many cells (A1 in all sheets) that have value greater than 0 value. The
sum portion in the numerator sums up all values in A1. Thus, the if
there are five sheets between First and Last (BTW, these should not
have anything in them anywhere), but only three have values greater
than 0, then the count function returns 3, and the sum function will
sum all cells (since adding 0 does not change the total).

Peo Sjoblom wrote:
Did you try that?

yes, several different combinations.

 

[Guy Hoffman]

My appologies for being a little thick headed but if another sheet i
added between First and Last that has a zero in cell A1, the formul
you described counts that sheet as if it did have a value greater tha
zero.

Your methodology does return the correct average if all sheets have
number greater than zero.

Please advise

 

[Guy Hoffman]

Peo,

I was able to revise your first formula to eliminate the namin
convention issue as follows:

=SUMPRODUCT(COUNTIF(INDIRECT({"'ABC'!$A1","'XYZ'!$A1"}),">0"))

with ABC and XYZ as the first and last sheet in the range. The fina
problem is that when I copy the formula down (dragging), the "A1
reference does not become "A2"

Any additional thoughts

 

[shades]

This is confusing! I have inserted sheets, with numbers and without
numbers, and the formula I posted works great. I inserted two extra
sheets, and the Count portion only returned 3 (which is what I
originally used with data in A1). In fact, I have severla different
combinations of adding or moving sheets, and the formul works great.

Oops, I see what happened. When I pasted into IE, I edited, and dropped
one item ("<") out of the formula.

=SUM(First:Last!$A$1)/COUNT(First:Last!$A$1,"<>0")

Sorry about that. What I was working with was fine, what ended up
posting was wrong.

 

[Guy Hoffman]

That indeed makes a difference provided the cell A1 is blank. If I pu
any number (positive, negative or a zero), it counts the sheet. Coul
you e-mail the sheet? Maybe I am just thick headed.

(e-mail address removed)

 

[shades]

I could email it, but see my backtrack edit on the previous post.

It would work, provided you had control over that cell by using an IF
statement, but with 180 sheets, that may be too much (although if they
are identical you could select all the sheets and put the IF statement
in all at once.

 

[Harlan Grove]

...
...

Oops, I see what happened. When I pasted into IE, I edited, and dropped
one item ("<") out of the formula.

=SUM(First:Last!$A$1)/COUNT(First:Last!$A$1,"<>0")

...

OK, you're testing, but you have no clue what you're testing. If you have two
blank worksheets named First and Last with two other worksheets between them,
which I'll call A and B, though their names don't matter, and if A!A1 contained
2 and B!A1 contained 4, then your formula does return 3. Now enter 0 in B!A1.
Your formula returns 1 rather than 2. Go on, try it.

What you're failing to recognize is that COUNT is *NOT* COUNTIF, so the "<>0"
arguement to COUNT does absolutely nothing. Your formula could be reduced to

=AVERAGE(First:Last!$A$1)

Change your formula so it uses COUNTIF, and you may discover that COUNTIF does
*NOT* accept 3D references. That being the case, only Peo Sjoblom has provided a
working answer in this thread.

 

[shades]

Yep. That's what I discovered. Thanks

 

[Guy Hoffman]

Peo's solution indeed works provided the sheet names are Sheet1
Sheet2,...

What if the sheet names are something else, like Tom, Dick and Jane

 

[Harlan Grove]

Peo's solution indeed works provided the sheet names are Sheet1,
Sheet2,...

What if the sheet names are something else, like Tom, Dick and Jane?

 

[Peo Sjoblom]

Here is a solution, open your workbook. Since you have 180 sheets you would
need a macro to get the sheet names, create a Summary sheet, put it first in
order, press Alt + F11 to open the VBE, click insert>module and paste in the
below

Sub List_Sheets()
Dim iSheet As Long
Application.ScreenUpdating = False
With Worksheets
For iSheet = 2 To .Count
ActiveCell(iSheet, 1).Value = .Item(iSheet).Name
Next iSheet
End With
Application.ScreenUpdating = True
End Sub


press Alt + Q to close the VBE. Save the workbook. Select a cell where you
want the sheet names list and press
Alt + F8 to run the macro named List_Sheets

Now select the whole list (if everything was ok you should have a list
starting with second list returning all sheet names)
click in the name box or do insert>name>define and give the list a name like
MySheets

Now use this formula

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