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

Data Analysis

  • Conditional Formatting Icon Sets Examples in Excel
  • Create Scatter Chart in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • How to Use Solver Tool in Excel

References

  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Lookup entire row in Excel
  • Basic INDEX MATCH approximate in Excel
  • How to get address of named range in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation allow weekday only
  • Excel Data validation must begin with
  • Prevent invalid data entering in specific cells
  • Excel Data validation must contain specific text
  • Excel Data validation don’t exceed total

Count unique text values with criteria

by

This tutorial shows how to Count unique text values with criteria using the example below;

Formula

{=SUM(--(FREQUENCY(IF(criteria,MATCH(values,values,0)),ROW(values)-ROW(valuesfirstcell)+1)>0))}

Explanation

To count unique values in a range with a criteria, you can use an array formula based on the FREQUENCY function. Assume you have a list of employee names together with hours worked on “Project X”, and you want know how many employees worked on that  project. Looking at the data, you can see that the same employee names appear more than once, so what you want is a count of the unique names. In the example shown, the formula in G6 is:

 

{=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0))}

Note: this is an array formula and must be entered with control + shift + enter.

How this formula works

This formula uses FREQUENCY to count unique numeric values that are derived with the MATCH function, which matches all values against themselves to determine a position.

Working from the inside, the MATCH function is used to get the position of each item that appears in the data. Because MATCH only returns the position of the “first match” values that appear more than once in the data return the same number.

Just outside of MATCH, the IF + criteria “filter” the values that MATCH works with so that it only returns MATCHES for rows that match criteria.

In the end, the array of positions generated by MATCH are fed to FREQUENCY in the data array argument.

The bins array argument is constructed from this part of the formula:

ROW(B3:B12)-ROW(B3)+1

which uses the row number of each item in the data and the row number of the first item in the data to build a straight, sequential array like this:

{1;2;3;4;5;6;7;8;9;10}

The FREQUENCY function returns an array of values that correspond to “bins”. In this case, we are supplying the same set of numbers for both the data array and bins array.

The result is that FREQUENCY returns an array of values that indicate the count that each value in the data array appears. This works because FREQUENCY is programmed to return zero for any numbers that appear more than once in the data array.

Next, each of these values is converted to TRUE or FALSE by the >0 construction, and then to 1 or zero with the double-unary (double-hyphen). This is done to force all non-zero values to 1.

Finally, SUMPRODUCT simply adds these values up and returns the total

Note: this is an array formula and must be entered using Control + Shift + Enter.

Handling empty cells in the range

If any of the cells in the range are empty, you’ll need to adjust the formula by adding an extra IF to prevent empty cells from being passed into the MATCH function, which will throw an error. The formula in G7 is:

{=SUM(--(FREQUENCY(IF(B5:B11<>"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0))}

With two criteria

If you have two criteria, you can extend the logic of the formula by adding another nested IF:

=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0))

Where c1 = criteria1, c2 = criteria2 and vals = the values range.

With boolean logic

With boolean logic, you can reduce nested IFs:

=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0))

This makes it easier to add additional criteria.

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 return blank in place of #DIV/0! error in Excel
  • IF, AND, OR and NOT Functions Examples in Excel
  • Not Equal To ‘<>‘ operator in Excel
  • How to use Excel FALSE Function
  • How to use IFS function in Excel

Date Time

  • Convert text timestamp into time in Excel
  • Basic timesheet formula with breaks in Excel
  • How to join date and text together in Excel
  • Get project end date in Excel
  • DATE function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Map text to numbers in Excel
  • How to randomly assign people to groups in Excel
  • Calculate conditional mode with criteria in Excel
  • Map inputs to arbitrary values in Excel
  • Group times into 3 hour buckets in Excel

General

  • Create dynamic workbook reference to another workbook in Excel
  • Mark Workbook as Final in Excel
  • How to test a range for numbers in Excel
  • Cell References: Relative, Absolute and Mixed Referencing Examples
  • How to create dynamic named range with INDEX in Excel
© 2026 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning