count number of values in one column if unique value in another.

I have two formula's I'm trying to work on. The first needs to count the number of "N" in one column if the value in the first column is unique.

i.e. if I had:

column A B
1 N
2 Y
1 N
3 N

I'd like it return 2 as there are 2 N's in column B which relate to unique values in column A. I have been trying to use a countif formul, but think I've not quite got it right as it returns zero. I have been trying the following:

=COUNT(IF(COUNTIF(A1:A4,A1:A4)=1, B1:B4, "N"))

The second forumla I need is a step further and would count the number of "Y" in a third column if the secon column is "N" and the first column is a unique value...

column A B C
1 N Y
2 Y Y
1 N Y
3 N Y

So again in this example the result I'd want is 2!

Any help would be great!

Thanks,
James

Hi,

Sorry if I'm missing something, but in your first example I see two values which are unique (2 and 3), but only one of which has an "N" in the corresponding row, so using your logic I would have a result of 1, not 2.

Please could you clarify?

Regards

Ok, can you try these two and get back to me?

Assuming your data is in the range A2:C11, this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER), should give you your first desired result:

=SUMPRODUCT(--(FREQUENCY(IF($B$2:$B$11="N",$A$2:$A$11),ROW(INDIRECT("1:"&MAX(IF($B$2:$B$11="N",$A$2:$A$11)))))>0))

And this (non-array, this time) should give you your second:

=SUMPRODUCT(($C$2:$C$11="Y")*($B$2:$B$11="N")*(COUNTIF($A$2:$A$11,$A$2:$A$11)=1))

Regards

Try

=SUMPRODUCT((A1:A4<>"")/COUNTIF(A1:A4,A1:A4)*(B1:B4="N"))

and

=SUMPRODUCT((A1:A4<>"")/COUNTIF(A1:A4,A1:A4)*(B1:B4="N")*(C1:C4="Y"))

Edit: Will not work if your range has blanks

Hi,

I assume you entered the first as an array formula, as I specified?

I've just tried both on a dataset of two columns of 25,000 rows and no issues whatsoever - a fraction of a second to calculate. What else have you got going on in your sheet, formula-wise? Have you tried running this scenario with just those two columns, as I did?

It would be difficult for me to help you further without seeing your actual sheet.

Regards

No worries. Glad I could help.