Search for string based on two columns

[Harroon]

Hello,
I have two columns
Priority , Status
For priority i have P1,P2,P3
Status = open,closed

I want to find basically how many P1 are open based on some formula

I used countif function to find how many P1,P2,P3 but i want to know ho
many of those P1 are open and how many P1 are closed.

I want to calculate these metrics on different sheet.

Any help please

 

[Claus Busch]

Hi,

Am Wed, 3 Oct 2012 15:14:16 +0000 schrieb Harroon:

I have two columns
Priority , Status
For priority i have P1,P2,P3
Status = open,closed

I want to find basically how many P1 are open based on some formula

what version of Excel do you use?
xl2007 or later:
=COUNTIFS(A2:A100,"P1",B2:B100,"open")
earlier versions:
=SUMPRODUCT(--(A2:A100="P1"),--(B2:B100="open"))


Regards
Claus Busch

 

[Spencer101]

Hello,
I have two columns
Priority , Status
For priority i have P1,P2,P3
Status = open,closed

I want to find basically how many P1 are open based on some formula

I used countif function to find how many P1,P2,P3 but i want to know ho
many of those P1 are open and how many P1 are closed.

I want to calculate these metrics on different sheet.

Any help please?

Hi, have a look at the attachment.
I've included dummy data on one worksheet and the formulas on another.

You don't state which version of Excel you're using so I've included tw
formula versions. SUMPRODUCT will work in all Excel versions an
COUNTIFS will work only in 2007 and later versions.

I think I've understood your need but let me know if not.
Also let me know if you need any of it explained.

S

+-------------------------------------------------------------------
|Filename: Harroon Example.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=610
+-------------------------------------------------------------------