COUNTIF across sheets

[MaggieMagill]

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'!D39,">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]

Try...

=SUMPRODUCT(COUNTIF(INDIRECT(ROW(INDIRECT("1:17"))&"!D39"),">0"))

Hope this helps!

 

[Not Me]

Subject: Re: COUNTIF across sheets
From: Domenic <[email protected]>
Newsgroups: microsoft.public.excel.worksheet.functions

Try...

=SUMPRODUCT(COUNTIF(INDIRECT(ROW(INDIRECT("1:17"))&"!D39"),">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!