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!

 

[Jimmian]

Hi

I'm arriving late to this party but this thread came up on Google and it seems to be dealing with an issue similar to one I'm having. I'm really not very good with functions and formulae, but managed to come up with something that worked for what I needed.

The following formula calculates fine in Excel 2007 but appears as below when opened in Excel 2003, with a #NAME? error. The _xlfn. part appears to have been added during conversion

=_xlfn.COUNTIFS('Appeals Register'!A2:A101,">=01/05/2011",'Appeals Register'!A2:A101,"<=31/05/2011")

I've tried using the SUMPRODUCT function that a few other people have suggested, and can get the formula to calculate correctly (i.e. not come up as a #NAME? or #VALUE! error) but I can't actually get it to count properly. The cell just displays 0 in all cases.

The formula I now have entered is:

=SUMPRODUCT('Appeals Register'!A2:A101=">=01/04/2011",'Appeals Register'!A2:A101="<=30/04/2011")

I'd really appeciate it if someone could help. I need to count the number of appeals received monthly (amongst other things) so if I can get this working I should be able to replicate it for the rest.

Thanks in advance

J