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.
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.
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.”
Here is the Query formula and result.
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.
Thank you so much! I wish I could have found this solution sooner!
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)))
Thank you so much!!!
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.
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)))
Well done, Prashanth
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?
https://infoinspired.com/google-docs/spreadsheet/running-count-in-google-sheets/
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?
Hi, Hellen,
For that, you can use Counta + Unique (if text string in column A) or Count+Unique (if numbers in column A).
=COUNTA(unique(A1:A))
Hey Prashanth,
Perfect. Thanks.
Cheers.
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.
Hi,
You can use this Query.
=query(A1:C,"Select A, Count(A) where B=date '"&TEXT(E2,"yyyy-mm-dd")&"' and C='Y' group by A",1)
In this formula, the cell E2 contains the criterion, i.e. the ‘specific date’.
How to Use Date Criteria in Query Function in Google Sheets [Date in Where Clause]
Best,
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!