HomeGoogle DocsSpreadsheetHow to Use COUNTIF with UNIQUE in Google Sheets

How to Use COUNTIF with UNIQUE in Google Sheets

Published on

Here we can learn how to use Countif with Unique in Google Sheets. It is a single-column approach. For multi-column, there is a different approach.

When you want to count a column based on the unique values in another column, using the COUNTIF function won’t work. You may want to use a Query formula for multi-column Unique Countifs.

We can learn both these tips (single and multi-column approaches) in this Google Sheets Tutorial.

Related: Learn Al Google Sheets Count Functions at One Place

I want to emphasize that my tutorial below does not make use of the Countunique function.

The Countunique only returns the number of unique values in a range, not how many times each unique item appears.

Usage of COUNTIF with UNIQUE in Google Sheets

To understand what I am trying to explain, see the sample data first. In this example, I want to count the number of receipts of all fruits, which is in column A.

COUNTIF with UNIQUE

You can use a Countif formula as below to count the receipt of a single item.

=COUNTIF(A2:A8,"APPLE")

It would output the result 5. But I want something different.

I want to apply Unique in column A and together Countif with it. The expected result would be as follows.

Unique a Column and Count

Here is the formula for this.

{UNIQUE(A2:A8),ARRAYFORMULA(COUNTIF(A2:A8,UNIQUE(A2:A8)))}

I will tell you how to use COUNTIF with UNIQUE in Google Sheets as above.

The above formula clubs two arrays by using Curly Brackets.

They are UNIQUE(A2:A8) and ARRAYFORMULA(COUNTIF(A2:A8,UNIQUE(A2:A8)))

While the first part of the formula, i.e., UNIQUE, returns the unique text from Column A, the second Countif formula counts the unique texts.

The curly brackets output them together as a single array.

Here is an equivalent Query formula for the above.

=QUERY(A:C,"SELECT A, COUNT(B) WHERE A <> '' GROUP BY A ORDER BY COUNT(B) DESC LABEL COUNT(B) 'TOTAL' ",1)

The above is a single-column approach. Usually, for multi-column conditional counting, the suggested formula is Countifs.

Must Check: Learn All Popular Google Sheets Functions

I will tell you how to do Countifs based on the Unique value in one column. Here instead of Countifs, the better solution is Query.

Similar: Use Unique and SUMIF together in Google Sheets

The Countifs Based on Unique Values

In this example, I want to count the number of present days for each student. That means count column C if it is equal to “Y.”

Countifs and Unique in Google Sheets

Here is the Query formula and result.

multiple column unique count

Conclusion

To properly learn and understand our tutorials, create the sample data (mockup) in a blank Sheet. You may keep the same range and apply the formula.

Sometimes a copied formula may not work, so better directly type it in your Sheets.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing 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.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

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