✦Template Printable Ideas Insights

Mastering "COUNTIF in Sheets": Step-by-Step Guide

By

Google Sheets is a powerful tool for data analysis, and one of its most useful functions is the COUNTIF function. COUNTIF allows you to count the number of cells that meet a specific criterion, making it easier to summarize and analyze data. Here's a comprehensive guide on how to use COUNTIF in Google Sheets.

Whether you're tracking sales, managing inventory, or analyzing student grades, COUNTIF can simplify your data analysis process. In this article, we'll explore how to use the basic COUNTIF function, as well as its more advanced applications, such as counting with multiple criteria and performing counts on non-contiguous ranges.

The Basic COUNTIF Function

The basic syntax for the COUNTIF function is: COUNTIF(range, criteria). Here, the range is the set of cells you want to evaluate, and the criteria determine which cells within the range will be counted.

How to use COUNTIF function in Excel and Google Sheets?
How to use COUNTIF function in Excel and Google Sheets?
COUNTIF Function
COUNTIF Function
How to Use COUNTIF on Google Sheets, Infographic
How to Use COUNTIF on Google Sheets, Infographic

Let's take an example where you have a list of scores (A2:A10) and you want to count how many students scored above 80. You would use the formula: COUNTIF(A2:A10, ">", 80).

Counting Text or Dates

COUNTIF isn't limited to numeric values. You can also use it to count text or dates. For instance, if you have a list of names (A2:A10) and you want to count how many of them end with "Smith", use the formula: COUNTIF(A2:A10, "*Smith"). To count dates from January 1, 2021, use: COUNTIF(B2:B10, ">=1/1/2021").

COUNT vs COUNTA vs COUNTBLANK vs COUNTIF - Explained Clearly
COUNT vs COUNTA vs COUNTBLANK vs COUNTIF - Explained Clearly
Excel COUNTIF function examples - not blank, greater than, duplicate or unique
Excel COUNTIF function examples - not blank, greater than, duplicate or unique
Use COUNTIF with Conditional Formatting in Excel
Use COUNTIF with Conditional Formatting in Excel

Note that when counting text, * acts as a wildcard, standing in for any combination of characters. So, "John*" matches "John Doe" but not "Adam".

Counting Odd or Even Numbers

You can also use COUNTIF to count odd or even numbers. For odd numbers, use the formula COUNTIF(range, "ISODD()"). For even numbers, use COUNTIF(range, "ISEVEN()").

Pro tip: Instead of retyping the range every time you write a new COUNTIF formula, you can drag the range down to automatically extend it.

How to use the COUNTIF formula in excel 😱
How to use the COUNTIF formula in excel 😱
How to Use the COUNTIF Function in Google Sheets
How to Use the COUNTIF Function in Google Sheets
Google Sheets Guide: COUNTIF Function
Google Sheets Guide: COUNTIF Function
How to Clean Numerical Data in Excel Using COUNT and COUNTIF
How to Clean Numerical Data in Excel Using COUNT and COUNTIF
COUNT functions in google sheet or excel
COUNT functions in google sheet or excel
How to Use COUNTIF Formula in microsoft Excel 😱
How to Use COUNTIF Formula in microsoft Excel 😱

Counting with Multiple Criteria (COUNTIFS)

When you need to count cells based on multiple criteria, use the COUNTIFS function. Its syntax is: COUNTIFS(criterion_range1, comparison1, [criterion_range2, comparison2], ...).

For example, if you want to count the number of students who scored above 80 on a math test (A2:A10) and live in the city (B2:B10), use the formula: COUNTIFS(A2:A10, ">", 80, B2:B10, "=City").

Non-Contiguous Ranges

By default, COUNTIF and COUNTIFS can only count cells in a single, contiguous range. However, you can use the SUM function along with COUNTIF to count non-contiguous ranges. Suppose you have scores in A1:A5 and B1:B5, and you want to count how many are above 80. Use the formula: SUM(COUNTIF(A1:A5, ">80"), COUNTIF(B1:B5, ">80")).

This is the magic of Unique and CountIF formulas
This is the magic of Unique and CountIF formulas
Learn about COUNTIF function in Google Sheets
Learn about COUNTIF function in Google Sheets
Master IF, COUNTIF & SUMIF in Google Sheets | Beginner Data Analytics Skills
Master IF, COUNTIF & SUMIF in Google Sheets | Beginner Data Analytics Skills

The same principle applies to COUNTIFS. If you have data in multiple, non-contiguous ranges, wrap each COUNTIFS function in a SUM function to add up the counts from each range.

Counting Blank Cells

To count blank cells, use the COUNTIF function with an empty cell as the criteria. For instance, to count the number of blank cells in A2:A10, use the formula: COUNTIF(A2:A10, "") or COUNTIF(A2:A10, "="").

Pro tip: If you're constantly updating a COUNTIF formula, you can add =COUNTIF( to your text box, then select the range and criteria. Google Sheets will auto-complete the formula for you.

Now that you're well-versed in how to use COUNTIF in Google Sheets, you're equipped to streamline your data analysis and gain valuable insights from your spreadsheets. Happyificing!