count unique values of 2 columns with criteria

**kbalasub** · 01-13-2013, 11:51 PM

Hello,
The following is my data:
A B C D
Red Good .... 1
Blue Bad .... 3
Red Bad .... 1
Orange Good .... 9
Green Worse .... 2
Blue Good .... 4
Yellow Worse .... 4
White Good .... 1
Blue Good .... 4
Green Good .... 2
Red Good .... 2

Requirement: Using a formula, I need to count for each colour in Column-A with the criteria "Good" in Column-B, the number of unique values in Column-D. For e.g. for Red with criteria Good, total unique values in Column-D is 2.
Ideally, I would like to copy the unique colours in another column(say E) and then count the unique values in D based on criteria in B and give the answer in a new column(say F) against each colour.
How can I achieve this?

Regards.
Bala

**FDibbins** · 01-13-2013, 11:57 PM

please upload a sample workbook. when you copy/paste here, it often does not come out as intened

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**kbalasub** · 02-05-2013, 08:33 AM

Hello,
I have attached the sample file. In this, I need to find the following:
Find the no.of unique testers who did the Product Test & Product Validation(Colum1) for the "OK" testcases(Column4).

Regards,
Bala

**Fotis1991** · 02-05-2013, 09:35 AM

In E1 and copy down.

=IF(AND(OR(D2="OK");OR(A2="ProductTest";A2="ProductValidation"));1;"")

In G2 and copy down.

=IFERROR(INDEX($B$2:$B$31;SMALL(IF(ISNA(MATCH($B$2:$B$31;$G$1:G1;0));IF($E$2:$E$31=1;ROW($B$2:$B$31)-ROW($B$2)+1));1));"")

**kbalasub** · 02-15-2013, 01:46 AM

Hi Fotis,
Thanks for the solution. But I have some difficulties. The no.of rows running to 10,000 and so it is difficult to copy down. Is there a formula to count the unique values(with text) with multiple criteria?

Bala

**Fotis1991** · 02-15-2013, 04:37 AM

Took me less than a minute to drag down the formulae! So for me wasn't difficult at all!

**Marcol** · 02-15-2013, 06:52 AM

Originally Posted by kbalasub

Hi Fotis,
... Is there a formula to count the unique values(with text) with multiple criteria?

Bala

Try using Dynamic Names, this workbook uses Fotis' idea, but for 2003, you can use IFERROR() in the formula

Formula:

Please Login or Register  to view this content.

Either way these are CSE arrays, and must be confirmed with Ctrl+Shift+Enter not just Enter.

Hopefully this should work for any length your data table might be.

Does this work for you?

[EDIT]
Server to busy ... try again later
More crashes causing errors!
When are the "Tech Wizards" going to stop f***ing about, this has been going on for weeks now.
Can they really be this incontinent, sorry, incompetent? ...

**kbalasub** · 02-16-2013, 09:37 AM

Thanks for your immediate response. The file I uploaded was only a sample. My actual file has more 10,000 rows and it is not easy to drag or copy for 10,000 rows. Also, this requirement has to be implemented repeatedly for multiple projects. So the best would be a formula which can calculate unique text values with multiple criteria.