< Go Back

How to Count Unique Numeric Values with Criteria

If you want to count unique numeric values with criteria, it is easy with Excel. What you will need to do is to combine three functions – SUM, FREQUENCY, and IF. This combination will return your desired result. 

Generic Formula to Count Unique Numeric Values with Criteria

{=SUM(--(FREQUENCY(IF(criteria,values),value)>0))}

An Application Example

Assume you are managing two factories (X and Y), and you have a list of the employees with logged hours. You want to find out how many workers logged time in each factory. In cases where the same workers appear several times in the list, the formula can help count unique employees per factory. Check out this example:

Figure 1. Example 1 of SUM, IF and FREQUENCY Functions

The formula in cell I15:

=SUM(--(FREQUENCY(IF(B6:B15=”A”,C6:C15,C6:C15)>0))

How the SUM and FREQUENCY Formula Works

The FREQUENCY function in the formula will return an array of values corresponding to “bins.” For this example, you need to supply “filtered” Worker number (Wkr No.) for the data array, as well as the full set of Work No.(s)  for the bin array. The IF function will filter do the filtering in this formula:

=IF(B6:B15=”A”,C6:C15,C6:C15)

This is the kind return that you get after the filtering is done:

{DM94;DM98;DM98;DM73;DM73;FALSE;FALSE;FALSE;FALSE;FALSE}

….you can notice that all Worker Numbers that are in Factory X are returned as FALSE.

The FREQUENCY will then work on the ‘bins” and return an array of values that give a count of each numeric value in the data array. The fact that the FREQUENCY function can automatically turn zero all numbers that appear more than once makes it effective in this example. Therefore, the results look like this:

{1;2;0;2;0;0;0;0;0;0;0}

When these values are tested to be greater than zero […)>0))}], this is what you get:

{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Further breakdown numerical (1 or zero) gives this:

=SUM {1;1;0;1;0;0;0;0;0;0;0}

…..where the SUM function adds these values to return a total of 3 for this problem.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar