The formula to count unique numeric values with criteria

Suppose you have a list of students that have present in two class: class A, and class B. You want to know how many unique student present in each class. Since the same student numbers appear more than once in the list, you need a formula that will count unique Student IDs per building. To do that in excel, you can use a formula based on the SUM and FREQUENCY functions, together with the FUNCTION to apply criteria.

The general formula: =SUM(--(FREQUENCY(IF(criteria,range),range)>0))

  • In this case FREQUENCY function – calculates how often values occur within a range of values, and then returns a vertical array of numbers.
  • range‘ is range of cells

Example:

A B
Class Student ID
1 A SE04171
2 A SE04171
3 A SE04172
4 A SE04173
5 B SE03669
6 B SE03678
7 B SE04171
8 B SE03576

Formula: {=SUM(--(FREQUENCY(IF(A1:A8="A",B1:B8),B1:B8)>0))}

The result is: 3