COUNTIF function

  • Thread starter Thread starter care4k9
  • Start date Start date
C

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!
 
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"))
 
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.
 
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.
 
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
 
Back
Top