Count Unique Values That Meet Criteria

[masouder]

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?

Office	Name	Sale $
1	Joe	$100
2	Sally	$200
1	Joe	$300
2	Mark	$100
1	Lisa	$400
1	Betty	$200
1	Lisa	$100
2	Mark	$300
1	Lisa	$500
2	Sally	$200

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​
1​	Office	Name	Sale $
2​	1​	Joe	100​		3​
3​	2​	Sally	200​
4​	1​	Joe	300​
5​	2​	Mark	100​
6​	1​	Lisa	400​
7​	1​	Betty	200​
8​	1​	Lisa	100​
9​	2​	Mark	300​
10​	1​	Lisa	500​
11​	2​	Sally	200​

<tbody>
</tbody>

E2=SUM(IF(FREQUENCY(IF(A2:A11=1,MATCH(B2:B11,B2:B11,0)),ROW(B2:B11)-ROW(B2)+1),1))

Control+Shift+Enter

 

[masouder]

That worked, thank you.