Counting unique values in Excel

Question

I have a list that looks like this:

Location   ID
______________
Boston     12
Boston     12
Boston     12
Boston     57
Boston     99
Chicago    12
Chicago    13
...

For each Location I want to count up the number of unique IDs. Boston would have 3 and Chicago would have 2 for example.

I can use Advanced filter by unique records but I want to know how to do this using an Excel formula.

@Jorvik77 Yes. I have a list of unique locations and I'm using that list to feed into the formula to do the counting. — KaliMa, May 11 '17 at 16:30

score 1 · Accepted Answer · answered May 11 '17 at 16:35

1

Use this array formula:

=SUM(IF($A$2:$A$8=D2,1/COUNTIFS($B$2:$B$8,$B$2:$B$8,$A$2:$A$8,D2)))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

answered May 11 '17 at 16:35

Scott Craner

148,073
10
49
81

How did you get your Excel to look dark gray like that? – KaliMa May 11 '17 at 16:41
Its under Account under the File tab. – Scott Craner May 11 '17 at 16:43
To add to this. If you didn't know the locations you could use this code in Column D2 and drag it down to get your unique list of cities =INDEX($A$2:$A$8,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$2:$A$8),0,0),0)) – Jorvik77 May 11 '17 at 16:52

Counting unique values in Excel

1 Answers1