I am attempting to count the unique values in one column where a criteria is met in another column. (Assume the following data starts in cell A1.) I want to count the number of different Names in Office 1. (The answer is 3: Joe, Lisa and Betty.) I don't think the COUNTIFS function will work, but I suspect the SUMPRODUCT function would. How can I accomplish this?
[TABLE="width: 195"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Office[/TD]
[TD]Name[/TD]
[TD]Sale $[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Joe[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sally[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Joe[/TD]
[TD]$300[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mark[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Lisa[/TD]
[TD]$400[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Betty[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Lisa[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mark[/TD]
[TD]$300[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Lisa[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sally[/TD]
[TD]$200[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 195"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Office[/TD]
[TD]Name[/TD]
[TD]Sale $[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Joe[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sally[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Joe[/TD]
[TD]$300[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mark[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Lisa[/TD]
[TD]$400[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Betty[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Lisa[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mark[/TD]
[TD]$300[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Lisa[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sally[/TD]
[TD]$200[/TD]
[/TR]
</tbody>[/TABLE]
