count unique text values with criteria in different column

**Alohaey** · 05-31-2012, 07:55 AM

Hi folks

Trying to design a formula that outputs me the number of unique values (text & numbers, but ignoring blanks) from col B. However it should only count unique values that have the value "y" in column A. Any value in column B which has "n" in column A should be ignored in the count. Example file is attached.

I managed to get the formula for counting without the criteria based on column A:
=SUM(IF(FREQUENCY(IF(LEN(B1:B13)>0,MATCH(B1:B13,B1:B13,0),""), IF(LEN(B1:B13)>0,MATCH(B1:B13,B1:B13,0),""))>0,1))

But I can not figure out how to include the criteria to only count values in column B if they are "y" in column A.
Btw I`m using Excel 2010.

Cheers
Alohaey

example file.xlsx

**NBVC** · 05-31-2012, 08:00 AM

Try:

Please Login or Register  to view this content.

**Fotis1991** · 05-31-2012, 08:03 AM

Hi and welcome to the forum.

An easy way to do this, is using a helper & hidden column(Let's say C)

sO IN c2 and copy down put this.

=COUNTIF(B2:B3,B2)

Then, use this formula to get your result.

=COUNTIFS(A2:A13,"y",C2:C13,1)

Is this, works, for you?

**zbor** · 05-31-2012, 08:10 AM

Fotis, it's better to use =COUNTIF(B$2:B2,B2) since you don't depend on order in column.

But no matter of that, your solution probably won't work because it assuming that y is in last instance of unique word, and that don't need to be true.
(i.e. out n in A3)

**Alohaey** · 05-31-2012, 08:11 AM

Wow I`m impressed. That were probably the fastest responses I ever got in a forum and with exactly the answer I was looking for. Thank heaps to both of you.
Alohaey