Unique Values and Dependents

[DCKSN]

I'm looking to see if there's a formula that solves the problem below.

In column A, I have a series of application I.D.s; some can appear once, some twice, some three times etc...there is no way of knowing in advance how many times an application I.D. will appear.

In column B, I have a returned result of "Pass" or "Fail".

As per below

[TABLE="width: 500"]
<tbody>[TR]
[TD]APP100
[/TD]
[TD]Pass
[/TD]
[/TR]
[TR]
[TD]APP101
[/TD]
[TD]Pass
[/TD]
[/TR]
[TR]
[TD]APP101
[/TD]
[TD]Pass
[/TD]
[/TR]
[TR]
[TD]APP102
[/TD]
[TD]Pass
[/TD]
[/TR]
[TR]
[TD]APP102
[/TD]
[TD]Fail
[/TD]
[/TR]
[TR]
[TD]APP102
[/TD]
[TD]Fail
[/TD]
[/TR]
[TR]
[TD]APP103
[/TD]
[TD]Fail
[/TD]
[/TR]
[TR]
[TD]APP104
[/TD]
[TD]Fail
[/TD]
[/TR]
[TR]
[TD]APP104
[/TD]
[TD]Pass
[/TD]
[/TR]
[TR]
[TD]APP104
[/TD]
[TD]Pass
[/TD]
[/TR]
</tbody>[/TABLE]

So, APP 100 has passed as has APP 101.
However, APP 102 despite having one pass recorded, has failed on two counts and as such has failed.
Similarly, APP 104 has failed on one count and as such has failed.

So, if I was to count the apps as unique values, I'd get a result of 5.
If I count the unique apps that have passed, I get 2.
Overall pass rate on unique application I.D.s is 40%

Counting unique values in the first column is easy.
What formula can i put in place that allows me to count the unique I.D.s that have passed?


Many thanks.

 

[pgc01]

Hi
Welcome to the board

With the data in your example in A1:B10, try:

=COUNT(1/FREQUENCY(IF(B1:B10="Pass",IF(COUNTIFS(A1:A10,A1:A10,B1:B10,"Fail")=0,MATCH(A1:A10,A:A,0))),ROW(A1:A10)))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER

 

[DCKSN]

YES!

Outstanding work PGCO1!

Thanks so much, that one was driving me spare.

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[Haui]

You're welcome. Thanks for the feedback.

Is the part -- IF(B1:B10="Pass" -- really necessary as I don't see what it really adds to the formula?
I do realise, however, that it e.g. ensures that the formula still works correctly should there be other values in column B except for PASS/FAIL including no value (yet) entered; but I wasn't sure whether you had any other instances in mind to make the formula more robust when you came up with the formula.

Cheers

 

[pgc01]

Hi Haui

You're correct, if you are sure that both the values in column B are only Pass/Fail and there are no empty cells in column A then the first test is not necessary.

The formula I posted is easier to use. You can for ex. use always the same number of rows in the formula, since you don't care if there are empty cells. In my tests I used 100 but I could have used 1000 or 10000. You don't have to count the rows in the table each time you use the formula.
You can also divide de table in sections, like classes or groups of students with headers in each section.

Anyway, you are right, for the example posted by the OP the first test is not needed.

Thanks for making it clear.