COUNTIF and similar formulas
 

I am trying to set a COUNTIF formula. I have already done this for column C
and it works but now i want to set a formula so that if column c contains
"sick" or "medical" it should then look to column D and tell me the number
with "sick" or medical" in column C that have the result "referred to
manager" in column D.

C
Medial
Sick
Leave

D
Referred to Manager
Returned to Manager

COUNTIF and similar formulas
 

=sumproduct(((C3:C5="Medical")+(C3:C5="Sick"))*(D3 :D5="Referred to
Manager"))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"JR" wrote in message
...
I am trying to set a COUNTIF formula. I have already done this for column
C
and it works but now i want to set a formula so that if column c contains
"sick" or "medical" it should then look to column D and tell me the number
with "sick" or medical" in column C that have the result "referred to
manager" in column D.

C
Medial
Sick
Leave

D
Referred to Manager
Returned to Manager

COUNTIF and similar formulas
 

=SUMPRODUCT((C2:C200={"Medical","Sick"})*(D2:D200= "Referred to Manager"))


--
__________________________________
HTH

Bob

"Ashish Mathur" wrote in message
...
=sumproduct(((C3:C5="Medical")+(C3:C5="Sick"))*(D3 :D5="Referred to
Manager"))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"JR" wrote in message
...
I am trying to set a COUNTIF formula. I have already done this for column
C
and it works but now i want to set a formula so that if column c contains
"sick" or "medical" it should then look to column D and tell me the
number
with "sick" or medical" in column C that have the result "referred to
manager" in column D.

C
Medial
Sick
Leave

D
Referred to Manager
Returned to Manager

COUNTIF and similar formulas
 

Try this...

Use cells to hold your criteria:

F1 = Sick
F2 = Medical
G1 = Referred to Manager

=SUMPRODUCT(--(ISNUMBER(MATCH(C1:C20,F1:F2,0))),--(D1:D20=G1))

--
Biff
Microsoft Excel MVP


"JR" wrote in message
...
I am trying to set a COUNTIF formula. I have already done this for column C
and it works but now i want to set a formula so that if column c contains
"sick" or "medical" it should then look to column D and tell me the number
with "sick" or medical" in column C that have the result "referred to
manager" in column D.

C
Medial
Sick
Leave

D
Referred to Manager
Returned to Manager