ExcelBanter - Search for string based on two columns

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Search for string based on two columns (https://www.excelbanter.com/excel-worksheet-functions/447284-search-string-based-two-columns.html)

Search for string based on two columns

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?

Search for string based on two columns

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

Quote:

Originally Posted by Harroon (Post 1606045)

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.