COUNTIF across sheets
 

I'm trying to create a formula that will count the values that are more
than 0 in a range across multiple worksheets.

COUNTIF('1:17'!D3:D9,"0") is what the "wizard" comes up with I use it but
it errs. After discovering that COUNTIF is an invalid formula function for
a 3-D reference, I'm stumped.

I would COUNTIF on each sheet and then just SUM on the master overview
sheet (named RESULTS) but I have 36 individual ranges on each sheet to
apply it to.

COUNT, of course, adds any cell with O and I need to have the 0's within
each sheet.

Is there any other way to easily accomplish this?

Try...

=SUMPRODUCT(COUNTIF(INDIRECT(ROW(INDIRECT("1:17")) &"!D3:D9"),"0"))

Hope this helps!

In article <xrh1f.938$L24.192@lakeread01,
MaggieMagill wrote:

I'm trying to create a formula that will count the values that are more
than 0 in a range across multiple worksheets.

COUNTIF('1:17'!D3:D9,"0") is what the "wizard" comes up with I use it but
it errs. After discovering that COUNTIF is an invalid formula function for
a 3-D reference, I'm stumped.

I would COUNTIF on each sheet and then just SUM on the master overview
sheet (named RESULTS) but I have 36 individual ranges on each sheet to
apply it to.

COUNT, of course, adds any cell with O and I need to have the 0's within
each sheet.

Is there any other way to easily accomplish this?

Domenic wrote in news:domenic22-
:

Subject: COUNTIF across sheets
From: Domenic
Newsgroups: microsoft.public.excel.worksheet.functions

Try...

=SUMPRODUCT(COUNTIF(INDIRECT(ROW(INDIRECT("1:17")) &"!D3:D9"),"0"))

Hope this helps!



Brilliantly! What a big help THAT was! Thanks so much.

That one would have never happened if left to me. Way beyond my scope tho'
I DID think of SUMPRODUCT somewhere in my frustration but just couldn't
figure how to work it. Another solution for me to study and try to learn
from.

I can't believe how helpful this newsgrouip is! Smart people with the
answer for every problem!

Thanks again!