| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CONVERTING Excel 2003 Commands to 2007 | Excel Discussion (Misc queries) | |||
| Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
| Alternative for =countifs (in 2007) for Excel 2003 | Excel Discussion (Misc queries) | |||
| Need function that will work in Excel 2003 like "Countifs" in 2007 | Excel Worksheet Functions | |||
| Converting COUNTIFS to 2003 format | Excel Worksheet Functions | |||
Hybrid Mode
