While working with Excel, we are able to count unique numeric values in a data set by using the **SUM**, **FREQUENCY **and** IF **functions, with some help using the double negative “**—**”. This step by step tutorial will assist all levels of Excel users in counting unique numeric values based on a criteria.

*Figure 1. Final result: Count unique numeric values with criteria*

Final formula: ** {=SUM(--(FREQUENCY(IF(C4:C10="Coffee",B4:B10),B4:B10)>0))}**

**Syntax of SUM Function**

SUM adds all given values

**=SUM(number1,[number2],...])**

**number1**– any number, array or cell reference whose values we want to add- Only number1 is required; succeeding numbers are optional

**Syntax of the FREQUENCY function**

FREQUENCY determines how often values occur within a range, and returns a vertical array of numbers. Since FREQUENCY returns an array, it must be entered as an array formula.

FREQUENCY automatically returns zero for numbers that appear more than once in the data array

`=`

**FREQUENCY**(**data_array**, **bins_array**)

**data_array**– an array or reference to a set of values whose frequencies we want to count**bins_array**– an array or reference to intervals specifying how we want to group the values in data_array- FREQUENCY ignores blank cells

**Syntax of IF Function**

IF function evaluates a given logical test and returns a TRUE or a FALSE

`=`

**IF**(**logical_test**, [**value_if_true**], [**value_if_false**])

- The arguments “
**value_if_true”**and “**value_if_false**” are optional. If left blank, the function will return TRUE if the logical test is met, and FALSE if otherwise.

**Setting up Our Data**

Our table contains three columns: Product ID (column B), Category (column C) and Orders (column D). We want to determine the number of unique Product IDs in the category “Coffee” and record it in cell G3.

* Figure 2. Sample data to count unique numeric values with criteria*

**Count unique Product IDs in Coffee**

In order to count unique Product IDs in Coffee, we follow these steps:

**Step 1**. Select cell G3

**Step 2**. Enter the formula: `=SUM(--(FREQUENCY(IF(C4:C10="Coffee",B4:B10),B4:B10)>0))`

**Step 3**: Press Ctrl + Shift + Enter

FREQUENCY returns an array so our formula needs to be entered as an array formula by pressing Ctrl + Shift + Enter.

* Figure 3. Entering the formula using SUM, FREQUENCY and IF*

`=SUM(--(FREQUENCY(IF(C4:C10="Coffee",B4:B10),B4:B10)>0))`

Our data_array for FREQUENCY is the IF function

. IF returns the value in column B if the category in column C is “Coffee”. The result is an array like this: **IF(C4:C10="Coffee",B4:B10)**

`{FALSE;FALSE;FALSE;19003;19003;19004;19005}`

The FREQUENCY function then returns the number of times a value occurs in the array. However, it automatically returns a zero for values occurring more than once. The resulting array is:

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

The array is then tested to be greater than zero.

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

The double negative sign **“–”** converts the values **TRUE **and **FALSE **into **1 **and **0**, respectively.

The array becomes: `{0;0;0;1;0;1;1}`

Finally, the result in cell G3 is the sum of the values in the array which is 3. There are three unique Product IDs with the category “Coffee”: 19003, 19004 and 19005.

*Figure 4. Output: Count unique numeric values with criteria *

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment