converting COUNTIFS formula from Excel 2007 to 2003
 

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",ADMISSI ONS!$J$2:$J$5000,'INFO &
STATS'!B12)+COUNTIFS(ADMISSIONS!$I$2:$I$5000,"IN.B C",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,'I NFO
&
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!

converting COUNTIFS formula from Excel 2007 to 2003
 

You want Sumproduct, as in:
=SUMPRODUCT((ADMISSIONS!$I$2:$I$5000="IN.OTH")*(AD MISSIONS!$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


"LG" wrote in message
...
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",ADMISSI ONS!$J$2:$J$5000,'INFO &
STATS'!B12)+COUNTIFS(ADMISSIONS!$I$2:$I$5000,"IN.B C",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,'I NFO
&
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!

converting COUNTIFS formula from Excel 2007 to 2003
 

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

--
Biff
Microsoft Excel MVP


"LG" wrote in message
...
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",ADMISSI ONS!$J$2:$J$5000,'INFO &
STATS'!B12)+COUNTIFS(ADMISSIONS!$I$2:$I$5000,"IN.B C",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,'I NFO
&
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!

converting COUNTIFS formula from Excel 2007 to 2003
 

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" wrote:

You want Sumproduct, as in:
=SUMPRODUCT((ADMISSIONS!$I$2:$I$5000="IN.OTH")*(AD MISSIONS!$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


"LG" wrote in message
...
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",ADMISSI ONS!$J$2:$J$5000,'INFO &
STATS'!B12)+COUNTIFS(ADMISSIONS!$I$2:$I$5000,"IN.B C",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,'I NFO
&
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!




.

converting COUNTIFS formula from Excel 2007 to 2003
 

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

"LG" wrote in message
...
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" wrote:

You want Sumproduct, as in:
=SUMPRODUCT((ADMISSIONS!$I$2:$I$5000="IN.OTH")*(AD MISSIONS!$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


"LG" wrote in message
...
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",ADMISSI ONS!$J$2:$J$5000,'INFO
&
STATS'!B12)+COUNTIFS(ADMISSIONS!$I$2:$I$5000,"IN.B C",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,'I NFO
&
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!




.

converting COUNTIFS formula from Excel 2007 to 2003
 

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!

--
Biff
Microsoft Excel MVP


"Fred Smith" wrote in message
...
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

"LG" wrote in message
...
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" wrote:

You want Sumproduct, as in:
=SUMPRODUCT((ADMISSIONS!$I$2:$I$5000="IN.OTH")*(AD MISSIONS!$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


"LG" wrote in message
...
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",ADMISSI ONS!$J$2:$J$5000,'INFO
&
STATS'!B12)+COUNTIFS(ADMISSIONS!$I$2:$I$5000,"IN.B C",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,'I NFO
&
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!




.