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 how 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,

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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 two formula versions. SUMPRODUCT will work in all Excel versions and 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.