Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to count table columns in Excel
  • How to create Gauge Chart in Excel
  • Understanding Pivot Tables in Excel
  • Excel Bar Chart
  • How to combine 2 or more chart types in a single chart in Excel

References

  • Count unique text values with criteria
  • How to use Excel COLUMN Function
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • Approximate match with multiple criteria in Excel
  • How to get last row in text data in Excel

Data Validations

  • Excel Data validation don’t exceed total
  • Excel Data validation require unique number
  • Excel Data validation allow uppercase only
  • Excel Data validation must not contain
  • Excel Data validation exists in list

Count unique numeric values with criteria in Excel

by

This tutorial shows how to Count unique numeric values with criteria in Excel using the example below;

Formula

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

Explanation

To count unique numeric values in a range with criteria you can use a formula based on the SUM and FREQUENCY functions, together with the IF function to apply criteria.

For example, assume you have a list of employee numbers with hours logged in two different buildings: building A, and building B. You want know how many unique employees logged time in each building. Since the same employee numbers appear more than once in the list, you need a formula that will count unique Employee IDs per building. In the example shown, the formula in G6 is:

{=SUM(--(FREQUENCY(IF(B3:B12="A",C3:C12),C3:C12)>0))}

Note: this is an array formula and must be entered with Control Shift Enter (CSE) syntax

How this formula works

The FREQUENCY function returns an array of values that correspond to “bins”. In this case, we are supplying a “filtered” set of ids for the data array, and the full set of ids for the bins array. The filtering is done with the IF function here:

IF(B3:B12="A",C3:C12)

Which in the example returns this:

{81400;81405;81405;82364;82364;FALSE;FALSE;FALSE;FALSE;FALSE}

Notice all ids not in building A are FALSE. Next, FREQUENCY returns an array of values that represent a count for each numeric value in the data array. This works because FREQUENCY has a special feature that automatically returns zero for numbers that appear more than once in the data array. The resulting array looks like this:

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

Next, each of these values is tested to be greater than zero. The result looks like this:

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

Each TRUE in the list represents a unique number in the list, and we just need to add up the TRUE values with SUM. However, SUM won’t add up logical values in an array, so we need to first coerce the values into 1 or zero. This is done with the double-negative (–). The result an array of only 1’s or 0’s.

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

Finally, SUM adds these values up and returns the total, which in this case is 3.

Multiple criteria

You can extend the formula to handle multiple criteria like this:

{=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0))}

Post navigation

Previous Post:

How to use Excel CHOOSE Function

Next Post:

Customize Ribbon In Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel OR Function
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • IF with wildcards in Excel
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function example in Excel

Date Time

  • NETWORKDAYS function: Description, Usage, Syntax, Examples and Explanation
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • Calculate number of hours between two times in Excel
  • How to get Holiday Date from Year in Excel
  • Add workdays no weekends in Excel

Grouping

  • Running count group by n size in Excel
  • Map inputs to arbitrary values in Excel
  • If cell contains one of many things in Excel
  • Group times into 3 hour buckets in Excel
  • Map text to numbers in Excel

General

  • Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’
  • How to get amount with percentage in Excel
  • Hide and Unhide Columns or Rows in Excel
  • Lock Cells in a Worksheet Excel
  • How to count total columns in range in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning