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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Ref No.[/TD]
[TD]Name[/TD]
[TD]Call made?[/TD]
[/TR]
[TR]
[TD]001/17[/TD]
[TD]Smith[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]002/17[/TD]
[TD]Jones[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]003/17[/TD]
[TD]Williams[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]004/17[/TD]
[TD]Davies[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]004/17[/TD]
[TD]Davies[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]004/17[/TD]
[TD]Davies[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]005/17[/TD]
[TD]Thomas[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]006/17[/TD]
[TD]Johnson[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

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.