counting multiple criteria in one column

[martinbarnes]

Can anyone help me with this? i'm trying to incorporate the OR function with
a COUNTIF function

 

[Ron Coderre]

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]

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]

If you post your formula, we'll see what you're working with.


Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[martinbarnes]

=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$2308="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$2308="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]

Try this formula (in sections for readability):

=SUMPRODUCT((Repurchase_Tracking_Table!$B$2:$B$2308=A15)*
(ISNUMBER(MATCH(Repurchase_Tracking_Table!$U$2:$U$2308,
{"Deny","Loss Mit Deny"},0))+
(Repurchase_Tracking_Table!$U$2:$U$2308="")*ISNUMBER(MATCH(
Repurchase_Tracking_Table!$K$2:$K$2308,{"Deny","Loss Mit Deny"},0))))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[martinbarnes]

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!

 

[martinbarnes]

Ron....I got it to work. Thanks!

Try this formula (in sections for readability):

=SUMPRODUCT((Repurchase_Tracking_Table!$B$2:$B$2308=A15)*
(ISNUMBER(MATCH(Repurchase_Tracking_Table!$U$2:$U$2308,
{"Deny","Loss Mit Deny"},0))+
(Repurchase_Tracking_Table!$U$2:$U$2308="")*ISNUMBER(MATCH(
Repurchase_Tracking_Table!$K$2:$K$2308,{"Deny","Loss Mit Deny"},0))))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Coderre]

Good to know.....I'm glad you got it working.

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)