| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF 2 Conditions using Defined Names
SLA_GROUP is a defined name for a COL A
NO_ROLL_NUM is a defined name for COL B COUNTIF(SLA_GROUP,$C5) works but I'm trying to COUNTIF based on SLA_GROUP = C% and also were NO_ROLL_NUM = 11. any ideas? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF 2 Conditions using Defined Names
=SUMPRODUCT(--(SLA_GROUP=$C5),--(NO_ROLL_NUM = 11))
those ranges must be the same size. -- HTH RP "Paul Dennis" wrote in message ... SLA_GROUP is a defined name for a COL A NO_ROLL_NUM is a defined name for COL B COUNTIF(SLA_GROUP,$C5) works but I'm trying to COUNTIF based on SLA_GROUP = C% and also were NO_ROLL_NUM = 11. any ideas? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF 2 Conditions using Defined Names
Getting error #NUM!
"Bob Phillips" wrote: =SUMPRODUCT(--(SLA_GROUP=$C5),--(NO_ROLL_NUM = 11)) those ranges must be the same size. -- HTH RP "Paul Dennis" wrote in message ... SLA_GROUP is a defined name for a COL A NO_ROLL_NUM is a defined name for COL B COUNTIF(SLA_GROUP,$C5) works but I'm trying to COUNTIF based on SLA_GROUP = C% and also were NO_ROLL_NUM = 11. any ideas? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF 2 Conditions using Defined Names
Hi
Try something like: =SUMPRODUCT((SLA_GROUP="C%")*(NO_ROLL_NUM=11)) Andy. "Paul Dennis" wrote in message ... SLA_GROUP is a defined name for a COL A NO_ROLL_NUM is a defined name for COL B COUNTIF(SLA_GROUP,$C5) works but I'm trying to COUNTIF based on SLA_GROUP = C% and also were NO_ROLL_NUM = 11. any ideas? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF 2 Conditions using Defined Names
Getting error #NUM!
"Andy" wrote: Hi Try something like: =SUMPRODUCT((SLA_GROUP="C%")*(NO_ROLL_NUM=11)) Andy. "Paul Dennis" wrote in message ... SLA_GROUP is a defined name for a COL A NO_ROLL_NUM is a defined name for COL B COUNTIF(SLA_GROUP,$C5) works but I'm trying to COUNTIF based on SLA_GROUP = C% and also were NO_ROLL_NUM = 11. any ideas? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF 2 Conditions using Defined Names
Try this:
Redefine the "refers to" ranges of your defined names to include only rows 1 through 65535, instead of the whole column and NOT the whole column. (eg A1:A65365 instead of A:A) Then try your formula again. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Paul Dennis" wrote: Getting error #NUM! "Andy" wrote: Hi Try something like: =SUMPRODUCT((SLA_GROUP="C%")*(NO_ROLL_NUM=11)) Andy. "Paul Dennis" wrote in message ... SLA_GROUP is a defined name for a COL A NO_ROLL_NUM is a defined name for COL B COUNTIF(SLA_GROUP,$C5) works but I'm trying to COUNTIF based on SLA_GROUP = C% and also were NO_ROLL_NUM = 11. any ideas? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF 2 Conditions using Defined Names
This worked but I wanted to refer by defined name. It seems the only way
would be to combine both fields as a seperate field which is also something I didn't want to do. "Ron Coderre" wrote: Try this: Redefine the "refers to" ranges of your defined names to include only rows 1 through 65535, instead of the whole column and NOT the whole column. (eg A1:A65365 instead of A:A) Then try your formula again. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Paul Dennis" wrote: Getting error #NUM! "Andy" wrote: Hi Try something like: =SUMPRODUCT((SLA_GROUP="C%")*(NO_ROLL_NUM=11)) Andy. "Paul Dennis" wrote in message ... SLA_GROUP is a defined name for a COL A NO_ROLL_NUM is a defined name for COL B COUNTIF(SLA_GROUP,$C5) works but I'm trying to COUNTIF based on SLA_GROUP = C% and also were NO_ROLL_NUM = 11. any ideas? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF 2 Conditions using Defined Names
I'm sorry....I must not have been clear. Try redefining the range that is
referred to in your named ranges. InsertNamesDefine Select: SLA_GROUP Refers to: change this from an entire column to rows 1 through 65535 of that column) Repeat for the NO_ROLL_NUM named range Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Paul Dennis" wrote: This worked but I wanted to refer by defined name. It seems the only way would be to combine both fields as a seperate field which is also something I didn't want to do. "Ron Coderre" wrote: Try this: Redefine the "refers to" ranges of your defined names to include only rows 1 through 65535, instead of the whole column and NOT the whole column. (eg A1:A65365 instead of A:A) Then try your formula again. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Paul Dennis" wrote: Getting error #NUM! "Andy" wrote: Hi Try something like: =SUMPRODUCT((SLA_GROUP="C%")*(NO_ROLL_NUM=11)) Andy. "Paul Dennis" wrote in message ... SLA_GROUP is a defined name for a COL A NO_ROLL_NUM is a defined name for COL B COUNTIF(SLA_GROUP,$C5) works but I'm trying to COUNTIF based on SLA_GROUP = C% and also were NO_ROLL_NUM = 11. any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Rename Defined Names | Excel Worksheet Functions | |||
| How to delete all defined names from a workbook? | Excel Worksheet Functions | |||
| COUNTIF - multiple conditions | Excel Worksheet Functions | |||
| How can I use COUNTIF to sum for two conditions? | Excel Worksheet Functions | |||
| Combining Defined Names to New Name For Validation | Excel Worksheet Functions | |||
Hybrid Mode
