Inconsistent Array Count results
 

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",CO UNT('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).

Inconsistent Array Count results
 

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?


"Suzanne" wrote:

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",CO UNT('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).

Inconsistent Array Count results
 

Maybe

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

"Suzanne" wrote:

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",CO UNT('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).

Inconsistent Array Count results
 

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" wrote in message
...
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",CO UNT('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).

Inconsistent Array Count results
 

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).

"Duke Carey" wrote:

Maybe

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

"Suzanne" wrote:

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",CO UNT('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).

Inconsistent Array Count results
 

This formula didn't work.

"Bob Phillips" wrote:

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" wrote in message
...
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",CO UNT('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).