Inconsistent Array Count results

[Suzanne]

In my CY 2005 timekeeping workbook I have a count function that works fine.
In my identical CY 2006 workbook, the count function is not working fine.

The result I'm trying to get is:
1. If Other!C9 has something in it, count it +
2. If cells in Other!A16:A21 = "MAR" count instances of entries in range
C16:C21

{=(COUNT('Other'!C9))+(IF('Other'!A16:A21="MAR",COUNT('Other'!C16:C21)))}

I've checked formatting on both sheets ('Other' and the sheet with the
formula) -- everything is identical from one book to the next. And no, I
can't copy the working worksheet over.

These worksheets contain inspection dates for numerous facilities and the
number I'm trying to get is if more than one inspection was conducted during
a given month (in this case, March).

 

[Suzanne]

New discovery...
I'm now sure that the formula stops looking for "MAR" if it encounters
anything else in the list first (e.g., Other!A16="FEB")

So, perhaps the better question is how do I fix this so the data results
are obtained from the entire range of Other!A16:A21?

 

[Duke Carey]

Maybe

=COUNT('Other'!C9)+SUMPRODUCT(--(A16:A21="MAR")*ISNUMBER(C16:C21))

 

[Bob Phillips]

Try

=--('Other'!C9<>"")+SUMPRODUCT(--('Other'!A16:A21,"MAR"),--('Other'!C16:C21)
)

not an array formula


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Suzanne]

Thanks very much... Now I have to figure out how this formula came up with
the right response (I've not used sumproduct or isnumber before).

 

[Suzanne]

This formula didn't work.

Try

=--('Other'!C9<>"")+SUMPRODUCT(--('Other'!A16:A21,"MAR"),--('Other'!C16:C21)
)

not an array formula


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)