Counting number of cells with non-zero value across multiple worksheets

[SactoMike]

I am trying to accumulate the number of cells within a
range of worksheets that have a non-zero value and have
not been successful.

I understand that I have limited functions available when
using a 3-D reference - the only ones that I can see that
are applicable are SUM, COUNT, and COUNTA. However, I do
not want to summarize the values so that basically leaves
me with COUNT and COUNTA

I have tried: =COUNT('03-16:03-30'!B11) as well as
=COUNTA('03-16:03-30'!B7>0) (as well as many other
variations) will no luck coming up with the expected
results.

Basically, I am keeping a tally of a softball team and
would like to identify how many games a player has played
by counting the cell that contain the number of innings
played. If it is GT zero then I want to accumulate 1 to
the to number of games played. I have one summary
worksheet and an individual worksheet for each game played.

I have a requirement to have a zero value in the innings
played column - not a blank.

Any help would be appreciated - Thanks.

 

[kkknie]

Start with something like this:

=COUNT(Sheet1!A3:B4,Sheet4!C2:E3,Sheet3!C1:E3)

To get this I typed in =Count( then selected my first range. Then
typed a comma and selected my next range and so on finishing with
close parenthesis.

You may need to fool around with absolute references (i.e. using $A$
or A$1 or whatever) to get the value to copy down the rows on you ne
sheet correctly.

 

[Guest]

Thanks for the response:

However, the problem that I am having with the COUNT
function is that it counts the number of cells that have
numeric values and zero is considered to be a numeric
value. I only want to count those cells that are greater
than zero for a given cell across multiple worksheets
within the workbook. AND I was hoping to accumulate by
using a worksheet range rather than reference each
individual worksheet. - Mike

 

[Dave Peterson]

Myrna Larson and David Hager wrote some 3D functions.

At John Walkenbach's site:
http://j-walk.com/ss/excel/eee/eee003.txt

(Countif3d/sumif3d/sumproduct3d)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Peo Sjoblom]

Or use this workaround


=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"03-16";"03-17";"03-18";"03-19";"03-20";"0
3-21";"03-22";"03-23";"03-24";"03-25";"03-26";"03-27";"03-28";"03-29";"03-30
"}&"'!B7:B11"),">0"))


a better way since it sounds as though the dates and thus the sheet names
can be changed, put a list of all sheet names and refer to the list like in

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A15&"'!B7:B11"),">0"))


replace B7:B11 with the range or cell you want to check

 

[Guest]

Thanks, I will give it a try.

I was able to get it to work by using:
=COUNT('03-16:03-30'!B5)
However, the COUNT function counts all cells that have a
numeric value (zero is numeric). What I was able to do was
to substitute a '-' instead of a zero to indicate that no
innings were played. I would have preferred a zero, but I
guess that will have to do.

Your solution looks interesting. I am not sure what the
INDIRECT or the use of &'s does but I will play with it
and see what I can do.