Count Unique Values That Meet Criteria

masouder

New Member
Joined
Jul 5, 2013
Messages
48
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?

OfficeNameSale $
1Joe$100
2Sally$200
1Joe$300
2Mark$100
1Lisa$400
1Betty$200
1Lisa$100
2Mark$300
1Lisa$500
2Sally$200

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

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

MARZIOTULLIO

Well-known Member
Joined
Aug 22, 2015
Messages
767
A
B
C
D
E
1
OfficeNameSale $
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
 

Watch MrExcel Video

Forum statistics

Threads
1,098,873
Messages
5,465,211
Members
406,419
Latest member
ldp124

This Week's Hot Topics

Top