Here we can learn how to use Countif with Unique in Google Sheets. This 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 COUNTIF function won’t work. You should use a Query formula for multi column Unique Countifs. We can learn both this tips in this Google Sheets Tutorial.
Please don’t get confuse our below tutorial with Countunique function. Countunique only returns the number of unique values in a range. Not how many times a 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 just want to count the number of receipts of all fruits which is in column A.
As normal you can use a Countif formula as below to count the receipt of a single item.
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.
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.
While the first part of the formula, which is 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. Normally for multiple column conditional counting, the suggested formula is Countifs.
Must Check: Learn All Popular Google Sheets Functions
I will tell you how to countifs based on Unique value in one column. Here instead of Countifs the better solution is Query.
Countifs Based on Unique Values
In this example I want to count the number of present days of each students. That means, count Column C, if column C is Y.
Here is the Query formula and result.
To learn and properly understand our tutorials, just mimic the sample data in a blank spreadsheet keeping the range as it is and apply the formula. Some times copying a formula may not work. So better, directly type the formula in your sheets.