Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get last column number in range in Excel
  • Approximate match with multiple criteria in Excel
  • Last row number in range
  • Offset in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation

Data Analysis Examples

  • Excel Bar Chart
  • How To Compare Two Lists in Excel
  • Understanding Pivot Tables in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • How to calculate current stock or inventory in Excel

Data Validation Examples

  • Excel Data validation specific characters only
  • Data validation must not exist in list
  • Excel Data validation don’t exceed total
  • Excel Data validation no punctuation
  • Excel Data validation allow weekday only

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.

Worked Example:   Highlight approximate match lookup conditional formatting in Excel

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:

Worked Example:   Lookup lowest value in Excel

{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.

Worked Example:   INDEX and MATCH descending order in Excel

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:

Count unique values in a range with COUNTIF in Excel

Next Post:

Count unique numeric values with criteria 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
  • Extract word that begins with specific character in Excel
  • Get first name from full name — Manipulating NAMES in Excel
  • LEFT, LEFTB functions: Description, Usage, Syntax, Examples and Explanation
  • How to pad text to match equal length in Excel
  • Normalize text by removing punctuations, extra spaces and more in Excel
  • How to calculate future date say 6 months ahead in Excel
  • Add business days to date in Excel
  • Get last weekday in month in Excel
  • How to calculate next day of week in Excel
  • Get project end date in Excel
  • How to calculate present value of annuity in excel
  • IPMT function: Description, Usage, Syntax, Examples and Explanation
  • Compound Annual Growth Rate CAGR formula examples in Excel
  • How to calculate principal for given period in Excel
  • AMORLINC function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles