| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting multiple criteria in one column
Can anyone help me with this? i'm trying to incorporate the OR function with
a COUNTIF function |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting multiple criteria in one column
With a list of items in A1:A20
This formula returns the count of cells that contain Criteria1 or Criteria2: B1: =SUM(COUNTIF(A1:A20,{"Criteria1","Criteria2"})) or...you could reference a list... C1: Criteria1 C2: Criteria2 B1: =SUMPRODUCT(COUNTIF(A1:A20,C1:C2)) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... Can anyone help me with this? i'm trying to incorporate the OR function with a COUNTIF function |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting multiple criteria in one column
Ron,
That was very helpful...thanks. Unfortunately, it's not working because when i nest/insert the "COUNTIF" into my "IF" formula, I'm getting double the number. Is there another way to do it when it's inside an IF formula? I should probably be figuring this out myself but my brain is mushed out. "Ron Coderre" wrote: With a list of items in A1:A20 This formula returns the count of cells that contain Criteria1 or Criteria2: B1: =SUM(COUNTIF(A1:A20,{"Criteria1","Criteria2"})) or...you could reference a list... C1: Criteria1 C2: Criteria2 B1: =SUMPRODUCT(COUNTIF(A1:A20,C1:C2)) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... Can anyone help me with this? i'm trying to incorporate the OR function with a COUNTIF function |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting multiple criteria in one column
If you post your formula, we'll see what you're working with.
Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... Ron, That was very helpful...thanks. Unfortunately, it's not working because when i nest/insert the "COUNTIF" into my "IF" formula, I'm getting double the number. Is there another way to do it when it's inside an IF formula? I should probably be figuring this out myself but my brain is mushed out. "Ron Coderre" wrote: With a list of items in A1:A20 This formula returns the count of cells that contain Criteria1 or Criteria2: B1: =SUM(COUNTIF(A1:A20,{"Criteria1","Criteria2"})) or...you could reference a list... C1: Criteria1 C2: Criteria2 B1: =SUMPRODUCT(COUNTIF(A1:A20,C1:C2)) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... Can anyone help me with this? i'm trying to incorporate the OR function with a COUNTIF function |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting multiple criteria in one column
=SUM(IF(Repurchase_Tracking_Table!$B$2:$B$2308=A15 ,IF(Repurchase_Tracking_Table!$U$2:$U$2308="Final
Deny",1,0),0))+SUM(IF(Repurchase_Tracking_Table!$B $2:$B$2308=A15,IF(Repurchase_Tracking_Table!$U$2:$ U$2308="Loss Mit Deny",1,0),0))+SUM(IF(Repurchase_Tracking_Table!$B $2:$B$2308=A15,IF(Repurchase_Tracking_Table!$U$2:$ U$2308="",IF(Repurchase_Tracking_Table!$K$2:$K$230 8="Final Deny",1,0),0)))+SUM(IF(Repurchase_Tracking_Table!$ B$2:$B$2308=A15,IF(Repurchase_Tracking_Table!$U$2: $U$2308="",IF(Repurchase_Tracking_Table!$K$2:$K$23 08="Loss Mit Deny",1,0),0))) This one's a mess, Ron....thanks for looking. I definitely need to make this one a little more streamlined. Regards, Marty "Ron Coderre" wrote: If you post your formula, we'll see what you're working with. Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... Ron, That was very helpful...thanks. Unfortunately, it's not working because when i nest/insert the "COUNTIF" into my "IF" formula, I'm getting double the number. Is there another way to do it when it's inside an IF formula? I should probably be figuring this out myself but my brain is mushed out. "Ron Coderre" wrote: With a list of items in A1:A20 This formula returns the count of cells that contain Criteria1 or Criteria2: B1: =SUM(COUNTIF(A1:A20,{"Criteria1","Criteria2"})) or...you could reference a list... C1: Criteria1 C2: Criteria2 B1: =SUMPRODUCT(COUNTIF(A1:A20,C1:C2)) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... Can anyone help me with this? i'm trying to incorporate the OR function with a COUNTIF function |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting multiple criteria in one column
Try this formula (in sections for readability):
=SUMPRODUCT((Repurchase_Tracking_Table!$B$2:$B$230 8=A15)* (ISNUMBER(MATCH(Repurchase_Tracking_Table!$U$2:$U$ 2308, {"Deny","Loss Mit Deny"},0))+ (Repurchase_Tracking_Table!$U$2:$U$2308="")*ISNUMB ER(MATCH( Repurchase_Tracking_Table!$K$2:$K$2308,{"Deny","Lo ss Mit Deny"},0)))) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... =SUM(IF(Repurchase_Tracking_Table!$B$2:$B$2308=A15 ,IF(Repurchase_Tracking_Table!$U$2:$U$2308="Final Deny",1,0),0))+SUM(IF(Repurchase_Tracking_Table!$B $2:$B$2308=A15,IF(Repurchase_Tracking_Table!$U$2:$ U$2308="Loss Mit Deny",1,0),0))+SUM(IF(Repurchase_Tracking_Table!$B $2:$B$2308=A15,IF(Repurchase_Tracking_Table!$U$2:$ U$2308="",IF(Repurchase_Tracking_Table!$K$2:$K$230 8="Final Deny",1,0),0)))+SUM(IF(Repurchase_Tracking_Table!$ B$2:$B$2308=A15,IF(Repurchase_Tracking_Table!$U$2: $U$2308="",IF(Repurchase_Tracking_Table!$K$2:$K$23 08="Loss Mit Deny",1,0),0))) This one's a mess, Ron....thanks for looking. I definitely need to make this one a little more streamlined. Regards, Marty "Ron Coderre" wrote: If you post your formula, we'll see what you're working with. Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... Ron, That was very helpful...thanks. Unfortunately, it's not working because when i nest/insert the "COUNTIF" into my "IF" formula, I'm getting double the number. Is there another way to do it when it's inside an IF formula? I should probably be figuring this out myself but my brain is mushed out. "Ron Coderre" wrote: With a list of items in A1:A20 This formula returns the count of cells that contain Criteria1 or Criteria2: B1: =SUM(COUNTIF(A1:A20,{"Criteria1","Criteria2"})) or...you could reference a list... C1: Criteria1 C2: Criteria2 B1: =SUMPRODUCT(COUNTIF(A1:A20,C1:C2)) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... Can anyone help me with this? i'm trying to incorporate the OR function with a COUNTIF function |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting multiple criteria in one column
Hey Ron,
Thanks for sending that - I've never used those functions before. That's probably why I'm getting a "#VALUE!" when i execute it, even when i hit CTRL+SHIFT+ENTER... I'll keep trying..thanks again! "Ron Coderre" wrote: Try this formula (in sections for readability): =SUMPRODUCT((Repurchase_Tracking_Table!$B$2:$B$230 8=A15)* (ISNUMBER(MATCH(Repurchase_Tracking_Table!$U$2:$U$ 2308, {"Deny","Loss Mit Deny"},0))+ (Repurchase_Tracking_Table!$U$2:$U$2308="")*ISNUMB ER(MATCH( Repurchase_Tracking_Table!$K$2:$K$2308,{"Deny","Lo ss Mit Deny"},0)))) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... =SUM(IF(Repurchase_Tracking_Table!$B$2:$B$2308=A15 ,IF(Repurchase_Tracking_Table!$U$2:$U$2308="Final Deny",1,0),0))+SUM(IF(Repurchase_Tracking_Table!$B $2:$B$2308=A15,IF(Repurchase_Tracking_Table!$U$2:$ U$2308="Loss Mit Deny",1,0),0))+SUM(IF(Repurchase_Tracking_Table!$B $2:$B$2308=A15,IF(Repurchase_Tracking_Table!$U$2:$ U$2308="",IF(Repurchase_Tracking_Table!$K$2:$K$230 8="Final Deny",1,0),0)))+SUM(IF(Repurchase_Tracking_Table!$ B$2:$B$2308=A15,IF(Repurchase_Tracking_Table!$U$2: $U$2308="",IF(Repurchase_Tracking_Table!$K$2:$K$23 08="Loss Mit Deny",1,0),0))) This one's a mess, Ron....thanks for looking. I definitely need to make this one a little more streamlined. Regards, Marty "Ron Coderre" wrote: If you post your formula, we'll see what you're working with. Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... Ron, That was very helpful...thanks. Unfortunately, it's not working because when i nest/insert the "COUNTIF" into my "IF" formula, I'm getting double the number. Is there another way to do it when it's inside an IF formula? I should probably be figuring this out myself but my brain is mushed out. "Ron Coderre" wrote: With a list of items in A1:A20 This formula returns the count of cells that contain Criteria1 or Criteria2: B1: =SUM(COUNTIF(A1:A20,{"Criteria1","Criteria2"})) or...you could reference a list... C1: Criteria1 C2: Criteria2 B1: =SUMPRODUCT(COUNTIF(A1:A20,C1:C2)) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... Can anyone help me with this? i'm trying to incorporate the OR function with a COUNTIF function |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting multiple criteria in one column
Ron....I got it to work. Thanks!
"Ron Coderre" wrote: Try this formula (in sections for readability): =SUMPRODUCT((Repurchase_Tracking_Table!$B$2:$B$230 8=A15)* (ISNUMBER(MATCH(Repurchase_Tracking_Table!$U$2:$U$ 2308, {"Deny","Loss Mit Deny"},0))+ (Repurchase_Tracking_Table!$U$2:$U$2308="")*ISNUMB ER(MATCH( Repurchase_Tracking_Table!$K$2:$K$2308,{"Deny","Lo ss Mit Deny"},0)))) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... =SUM(IF(Repurchase_Tracking_Table!$B$2:$B$2308=A15 ,IF(Repurchase_Tracking_Table!$U$2:$U$2308="Final Deny",1,0),0))+SUM(IF(Repurchase_Tracking_Table!$B $2:$B$2308=A15,IF(Repurchase_Tracking_Table!$U$2:$ U$2308="Loss Mit Deny",1,0),0))+SUM(IF(Repurchase_Tracking_Table!$B $2:$B$2308=A15,IF(Repurchase_Tracking_Table!$U$2:$ U$2308="",IF(Repurchase_Tracking_Table!$K$2:$K$230 8="Final Deny",1,0),0)))+SUM(IF(Repurchase_Tracking_Table!$ B$2:$B$2308=A15,IF(Repurchase_Tracking_Table!$U$2: $U$2308="",IF(Repurchase_Tracking_Table!$K$2:$K$23 08="Loss Mit Deny",1,0),0))) This one's a mess, Ron....thanks for looking. I definitely need to make this one a little more streamlined. Regards, Marty "Ron Coderre" wrote: If you post your formula, we'll see what you're working with. Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... Ron, That was very helpful...thanks. Unfortunately, it's not working because when i nest/insert the "COUNTIF" into my "IF" formula, I'm getting double the number. Is there another way to do it when it's inside an IF formula? I should probably be figuring this out myself but my brain is mushed out. "Ron Coderre" wrote: With a list of items in A1:A20 This formula returns the count of cells that contain Criteria1 or Criteria2: B1: =SUM(COUNTIF(A1:A20,{"Criteria1","Criteria2"})) or...you could reference a list... C1: Criteria1 C2: Criteria2 B1: =SUMPRODUCT(COUNTIF(A1:A20,C1:C2)) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... Can anyone help me with this? i'm trying to incorporate the OR function with a COUNTIF function |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting multiple criteria in one column
Good to know.....I'm glad you got it working.
Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... Ron....I got it to work. Thanks! "Ron Coderre" wrote: Try this formula (in sections for readability): =SUMPRODUCT((Repurchase_Tracking_Table!$B$2:$B$230 8=A15)* (ISNUMBER(MATCH(Repurchase_Tracking_Table!$U$2:$U$ 2308, {"Deny","Loss Mit Deny"},0))+ (Repurchase_Tracking_Table!$U$2:$U$2308="")*ISNUMB ER(MATCH( Repurchase_Tracking_Table!$K$2:$K$2308,{"Deny","Lo ss Mit Deny"},0)))) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... =SUM(IF(Repurchase_Tracking_Table!$B$2:$B$2308=A15 ,IF(Repurchase_Tracking_Table!$U$2:$U$2308="Final Deny",1,0),0))+SUM(IF(Repurchase_Tracking_Table!$B $2:$B$2308=A15,IF(Repurchase_Tracking_Table!$U$2:$ U$2308="Loss Mit Deny",1,0),0))+SUM(IF(Repurchase_Tracking_Table!$B $2:$B$2308=A15,IF(Repurchase_Tracking_Table!$U$2:$ U$2308="",IF(Repurchase_Tracking_Table!$K$2:$K$230 8="Final Deny",1,0),0)))+SUM(IF(Repurchase_Tracking_Table!$ B$2:$B$2308=A15,IF(Repurchase_Tracking_Table!$U$2: $U$2308="",IF(Repurchase_Tracking_Table!$K$2:$K$23 08="Loss Mit Deny",1,0),0))) This one's a mess, Ron....thanks for looking. I definitely need to make this one a little more streamlined. Regards, Marty "Ron Coderre" wrote: If you post your formula, we'll see what you're working with. Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... Ron, That was very helpful...thanks. Unfortunately, it's not working because when i nest/insert the "COUNTIF" into my "IF" formula, I'm getting double the number. Is there another way to do it when it's inside an IF formula? I should probably be figuring this out myself but my brain is mushed out. "Ron Coderre" wrote: With a list of items in A1:A20 This formula returns the count of cells that contain Criteria1 or Criteria2: B1: =SUM(COUNTIF(A1:A20,{"Criteria1","Criteria2"})) or...you could reference a list... C1: Criteria1 C2: Criteria2 B1: =SUMPRODUCT(COUNTIF(A1:A20,C1:C2)) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "martinbarnes" wrote in message ... Can anyone help me with this? i'm trying to incorporate the OR function with a COUNTIF function |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting using multiple criteria | Excel Discussion (Misc queries) | |||
| Counting Using Multiple Criteria | Excel Worksheet Functions | |||
| counting using multiple criteria | Excel Discussion (Misc queries) | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Linear Mode
