Counting the number of 'yes' values for unique individuals

[Major_Sharpe]

Hello there,

I have hit a bit of a stumbling block in a spreadsheet I am attempting to make.

The sheet comprises of a unique reference number, a name, and a yes or no entry.

I have a sumproduct / countif formula to count the number of unique contractors (based on the ref number). However, what I need is to be able to count the number of the individual names, that have a "yes" in column C.

A sample of the sheet:

Ref No.	Name	Call made?
001/17	Smith	Yes
002/17	Jones	Yes
003/17	Williams	No
004/17	Davies	No
004/17	Davies	Yes
004/17	Davies	Yes
005/17	Thomas	No
006/17	Johnson	Yes

<tbody>
</tbody>

So, as you can see, there are occasions where a name/ ref no. will initially be a no. But will then later be a yes, sometimes twice. (004/17 Davies).

Can anyone help me with a formula that would count these yes', but only when they are unique. So Davies would count a yes, but only once? In the above example, the calculation should equal 4.

I really hope this makes sense, and apologies if it does not!

I am using Office 2007.

Many Thanks in advance.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($A$2:$A$9=""),IF($C$2:$C$9="yes",MATCH($A$2:$A$9,$A$2:$A$9,0))),ROW($A$2:$A$9)-ROW($A$2)+1),1))

 

[Major_Sharpe]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($A$2:$A$9=""),IF($C$2:$C$9="yes",MATCH($A$2:$A$9,$A$2:$A$9,0))),ROW($A$2:$A$9)-ROW($A$2)+1),1))

You are an absolute star. Thank you so much.

 

[Aladin Akyurek]

You are an absolute star. Thank you so much.

You are welcome.