converting COUNTIFS formula from Excel 2007 to 2003

[LG]

I work on excel 2007, but I have worksheets that are shared with people who
have 2003. I have formulas in 2007 and I need to convert them to a 2003
compatible format. I have tried many variations, but I can't get it to work.
Can you help:
Formula # 1:

=COUNTIFS(ADMISSIONS!$I$2:$I$5000,"IN.OTH",ADMISSIONS!$J$2:$J$5000,'INFO &
STATS'!B12)+COUNTIFS(ADMISSIONS!$I$2:$I$5000,"IN.BC",ADMISSIONS!$J$2:$J$5000,'INFO & STATS'!B12)

Formula # 2:

=COUNTIFS(ADMISSIONS!$B$2:$B$10000,"Y",ADMISSIONS!$I$2:$I$10000,"IN.OTH",ADMISSIONS!$J$2:$J$10000,'INFO
&
STATS'!B12)+COUNTIFS(ADMISSIONS!$B$2:$B$10000,"Y",ADMISSIONS!$I$2:$I$10000,"IN.BC",ADMISSIONS!$J$2:$J$10000,'INFO & STATS'!B12)

Thank you!

 

[Fred Smith]

You want Sumproduct, as in:
=SUMPRODUCT((ADMISSIONS!$I$2:$I$5000="IN.OTH")*(ADMISSIONS!$J$2:$J$5000,'INFO
&
STATS'!B12))+SUMPRODUCT((ADMISSIONS!$I$2:$I$5000="IN.BC")*(ADMISSIONS!$J$2:$J$5000='INFO
& STATS'!B12))

Regards,
Fred

 

[T. Valko]

Try these...

=SUMPRODUCT(--(ISNUMBER(MATCH(ADMISSIONS!I2:I5000,{"IN.OTH","IN.BC"},0))),--(ADMISSIONS!J2:J5000='INFO
& STATS'!B12))

=SUMPRODUCT(--(ISNUMBER(MATCH(ADMISSIONS!I2:I10000,{"IN.OTH","IN.BC"},0))),--(ADMISSIONS!B2:B10000="Y"),--(ADMISSIONS!J2:J10000='INFO
& STATS'!B12))

 

[LG]

I am getting a #VALUE! error response. I am using the SUMPRODUCT formula,but
it is not working. I need a count of entries, there aren't any numbers to sum.

 

[Fred Smith]

A #Value error results when the arrays are a different size. Are you sure
you copied the formula exactly as shown? Are you saying your Countifs works,
but this Sumproduct does not? We understand that you want a count, not a
sum.

Regards,
Fred

 

[T. Valko]

The problem is this:

....*(ADMISSIONS!$J$2:$J$5000,'INFO & STATS'!B12))...

Should be:

....*(ADMISSIONS!$J$2:$J$5000='INFO & STATS'!B12))...

Copy/paste has it's drawbacks!