Using COUNTIF with UNIQUE in Google Sheets: A Step-by-Step Guide

Published on

COUNTIF with UNIQUE and COUNTUNIQUEIFS are two different concepts in Google Sheets. The former is used to summarize a table, while the latter is used to get a unique count after applying criteria.

For example, if a table contains tree names in one column and their heights in another column (A1:B), you can use COUNTUNIQUEIFS to get the number of unique trees with heights greater than 20 meters.

Tree NameHeight (in meters)
Eucalyptus27
Maple15
Pine25
Redwood30
Willow12
Eucalyptus25
Eucalyptus24
Maple14
=COUNTUNIQUEIFS(A1:A, B1:B, ">20") // returns 3

In the same table, you can use COUNTIF with UNIQUE to generate a summary report where the first column contains the unique tree names and the second column contains the count of each tree.

We’ll explore this technique in this tutorial.

Step 1: Using UNIQUE to Extract Unique Items

To extract the unique items (in this case, tree names) from A2:A, excluding the header row, use the following formula in cell D2:

=UNIQUE(TOCOL(A2:A, 1))
Extracting unique items for counting in Google Sheets

The TOCOL function removes empty rows from the range. If you don’t use this function, UNIQUE may return unique records along with a blank cell at the bottom, which can cause issues when we combine COUNTIF with UNIQUE.

Step 2: Counting the Unique Items with COUNTIF

Enter the following COUNTIF array formula in cell E2 to count the unique items extracted:

=ArrayFormula(COUNTIF(A2:A, D2:D))

This will retain trailing zeros, which you might not be concerned about. However, the issue lies in using the extracted unique records range D2:D. To resolve this, simply replace D2:D with the formula from Step 1:

=ArrayFormula(COUNTIF(A2:A, UNIQUE(TOCOL(A2:A, 1))))
Example of using COUNTIF with UNIQUE in Google Sheets

The following two steps are optional.

Step 3: Combining COUNTIF with UNIQUE

In this step, we will combine both formulas to horizontally stack the counts with the unique values. We can use the HSTACK function with the following syntax:

HSTACK(range1, range2)

Here, range1 is the formula from Step 1, and range2 is the formula from Step 2:

=HSTACK(UNIQUE(TOCOL(A2:A, 1)), ArrayFormula(COUNTIF(A2:A, UNIQUE(TOCOL(A2:A, 1)))))

Remove the formulas in D2 and E2, and instead, enter this combined formula directly in cell D2.

Step 4: Simplifying COUNTIF with UNIQUE Using LET

The formula currently has repeated calculations, with the UNIQUE part appearing twice.

To simplify, we will assign the name ‘uni’ to the UNIQUE formula and ‘cnt’ to the COUNTIF formula using the LET function. Here’s the formula to be entered in cell D2:

=LET(uni, UNIQUE(TOCOL(A2:A, 1)), cnt, ArrayFormula(COUNTIF(A2:A, uni)), HSTACK(uni, cnt))

That’s how you can use COUNTIF with UNIQUE in Google Sheets.

QUERY as an Alternative Solution

If your data doesn’t contain mixed data types in a column (e.g., a column containing both numbers and text), you can use the QUERY formula as an alternative to the COUNTIF with UNIQUE formula.

You may get unexpected results when using a table with mixed data types in columns because QUERY might consider minority data types as null values.

In our sample data, there’s no such issue since the first column is text, and the second one is a number.

Here’s the QUERY formula:

=QUERY(A1:B, "SELECT A, COUNT(A) WHERE A <>'' GROUP BY A")
Using QUERY as an alternative to COUNTIF with UNIQUE in Google Sheets

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

17 COMMENTS

  1. Would there be a way of joining the name of the unique value and sum in a single cell with join( char(10))?

    Results would be;

    apples – 4
    Oranges -2

    • Hi, Daniel Hanford,

      We can achieve the count result as per your requirement.

      Assume the list in A2:A doesn’t contain a field label (header).

      =ArrayFormula(substitute(transpose(query(transpose(query(if(len(A2:A),A2:A&" -",),
      "Select Col1,count(Col1) where Col1 is not null group by Col1 label count(Col1)''")),,9^9)),
      "-",char(10)))

  2. Hello,

    I have just found out about your wonderful blog.

    I am working on Google Sheets, where people can create a list of items. I split those lists into separate cells.

    That made me have a table where each item is stored in individual cells.

    E.g.

    row1: item 1 | item 2 | item 4
    row2: item 4 | item 3 | item 1
    row3: item 2 | item 1 | item 3

    I would like to have a pie chart for counting the occurrence of items in all rows.

    I am not sure if my answer is posted already somewhere. If so, please share the link.

    Thank you so much

    • Hi, Hai Hoang,

      Assume the values after the split is in the range A1:C.

      If so, make the columns F and G empty. Then insert the below Query formula in cell F1.

      =query(flatten(A1:C),"Select Col1,count(Col1) where Col1 is not null group by Col1 label count(Col1)''")

      Use that output to plot the Pie chart.

  3. Hi! Is there a way to use the =COUNTA(UNIQUE formula to add a criterion, i.e., count unique text values if the amount is greater than x? Any suggestions would be wonderful! Thank you!!

    • Hi, MaryAnn,

      There is a specific function called COUNTUNIQUEIFS for this.

      =countuniqueifs(A2:A14,B2:B14,">5")
      It returns the count of unique texts in A2:A14 if corresponding numbers in B2:B14 are greater than 5.

      This Filter will also work.
      =counta(unique(filter(A2:A14,B2:B14>5)))

  4. What if I want to count Multiple Unique Values depends on the time I input it.

    Ex. If I input STA it will count 1 and if I input another STA below it will count 2 and so on.

    STA 1
    STA 2

    Is this possible?

  5. What if I need to count the unique values in one column / exclude duplicates, for example counting the number of individual students in column A? Is there a way to use Countif to do this?

  6. What if I would need this to work based on 2 filters? For the above example, I would like to count unique names that were present (Y) on a specific date? What would the query be? Thanks.

  7. This worked perfectly and save me a ridiculous amount of time. Thanks so much for the thorough explanation of how to set Google Sheets like this!

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.