How to Use COUNTIF with UNIQUE in Google Sheets

0
254
How to Use COUNTIF with UNIQUE in Google Sheets

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.

Related: Learn Al Google Sheets Count Functions at One Place

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.

COUNTIF with UNIQUE

As normal 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.

1. unique(A2:A8)

2. ArrayFormula(countif(A2:A8,unique(A2:A8)))

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.

Similar: Use Unique and SUMIF together in Google Sheets

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.

Countifs and Unique in Google Sheets

Here is the Query formula and result.

multiple column unique count

Conclusion:

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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here