COUNTIF function

[care4k9]

This formula will not work and can't figure out why. I need to count how
many movies we show in a month, and use the criteria of >0.
=COUNTIF('1:31'!B6,">0") HELP!

 

[Dave Smith]

Try something like this:

=SUMPRODUCT((C4:C8>0)*1)

HTH

-Dave

 

[J.E. McGimpsey]

I think you forgot the COUNTIF() criterion:

=SUMPRODUCT(COUNTIF(INDIRECT("" &
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,
23,24,25,26,27,28,29,30,31} &"!B6"),">0"))

Those darn 3D references! Try this:

=SUMPRODUCT(COUNTIF(INDIRECT("" &
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,
23,24,25,26,27,28,29,30,31} &"!B6"))

If you don't want to use this ugly formula you could use a
Countif on each sheet then Sum the Countifs. That's those
darned 3D references with very limited functionality to
evaluate across sheets!

Biff

-----Original Message-----
This formula will not work and can't figure out why. I need to count how
many movies we show in a month, and use the criteria of
0.
=COUNTIF('1:31'!B6,">0") HELP!



------------------------------------------------
[/url]
~~ View and post usenet messages directly from http://www.ExcelForum.com/

.

 

[J.E. McGimpsey]

This works for me:

=SUMPRODUCT(COUNTIF(INDIRECT(ROW(1:31) &"!B6"),">0"))

though it would be more stable as

=SUMPRODUCT(COUNTIF(INDIRECT(ROW(INDIRECT("1:31")) &"!B6"),">0"))

since the Row(1:31) reference would get updated if rows were
inserted or deleted.

But note that your method is more flexible if sheets are named
rather than simply using numbers.

It's important to remember that this is not a 3D reference like
other 3D references: the array-entered SUM(First:Last!B6) relies on
*position* of the sheets for whether they're included or not. For
example, if Sheet A was to the left of Sheet First, then it would
not be included in the SUM. Likewise if Sheet InTheMiddle was
deleted from between First and Last, the SUM() would still work for
the remaining sheets.

The formula above, however, creates an array of 31 COUNTIF() results
based only on name of the sheet, so it's independent of sheet
position. That means that deleting a sheet will break the formula,
too.

 

[care4k9]

Dave,

Thank you for your reply...

I'm not sure I understand this equation

=SUMPRODUCT((C4:C8>0)*1)

The cells that I want to calculate is B6 in every sheet of the
work book. I substituted B6, but it only counted that particular sheet.
I just need to know how many values are above 0. I thought that's what
the COUNTIF calcualtion is for. My help is extrememly limited and I
live in such a remote area that the nearest bookstore is 2 1/2 hour
drive from here. I have less than a week to solve this calculation.

 

[Biff]

Thanks J.E. - I was trying a version of your second
example but had the Row function as the argument for the
second Indirect.

Biff