How to Use COUNTIF with UNIQUE in Google Sheets

The Single-Column Approach

Before we dive into the tutorial, let’s take a quick look at the problem we’re trying to solve. Let’s say you want to count the number of receipts for each type of fruit in column A. Using the COUNTIF function alone won’t give you the desired result. But fear not, we have a solution!

Here’s the formula you need to use:

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

This formula combines two arrays using Curly Brackets. The first array, UNIQUE(A2:A8), returns the unique text from Column A. The second array, ARRAYFORMULA(COUNTIF(A2:A8,UNIQUE(A2:A8))), counts the occurrences of each unique text. Together, they output a single array with the desired result.

You can also achieve the same result using a Query formula:

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

The Multi-Column Approach

Now, what if you want to do conditional counting based on unique values in one column? Let’s say you want to count the number of present days for each student, where the value in column C is equal to “Y.” In this case, using COUNTIFS may not be the most efficient solution. Instead, you can use a Query formula.

Here’s the Query formula you need to use:

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

Conclusion

Learning how to use COUNTIF with UNIQUE in Google Sheets can greatly improve your data analysis and reporting capabilities. To truly grasp the concepts discussed in this tutorial, we recommend creating a sample dataset in a blank sheet and applying the formulas yourself. Remember, sometimes copied formulas may not work as expected, so it’s always better to type them directly into your Sheets.

Now that you’ve learned this valuable technique, you can take your data analysis skills to the next level. For more tips and tricks, visit Crawlan.com and enhance your Google Sheets expertise. Happy counting!