How to Use COUNTIF in Google Sheets

What COUNTIF Does and Why It Matters

COUNTIF is arguably one of the most useful formulas in Google Sheets. At its core, it does one simple job: count cells that match a specific condition. But that simple job opens the door to countless data analysis tasks that would otherwise require tedious manual counting or complex workarounds.

Whether you’re tracking inventory, analyzing survey data, managing attendance, or monitoring sales performance, COUNTIF helps you instantly answer questions like “How many sales did we make above 500 dollars?” or “How many customers are from California?” Without COUNTIF, you’d be clicking through your data one cell at a time, keeping a mental tally. With COUNTIF, you get an exact answer in milliseconds.

The formula works on text, numbers, dates, and even cells that contain formulas. It’s flexible enough to handle exact matches, partial matches, greater-than or less-than comparisons, and wildcard patterns. Once you understand the syntax, you’ll find yourself using COUNTIF in nearly every spreadsheet you create.

This guide covers everything you need to know about COUNTIF: the syntax, practical examples across different data types, comparisons with related formulas, and real-world use cases that show why this formula deserves a permanent place in your Google Sheets toolkit.

Understanding COUNTIF Syntax

The COUNTIF formula has a simple structure with two required arguments: =COUNTIF(range, criterion). Let’s break down what each part means.

The first argument, range, is the group of cells you want to search. This could be a single column like A2:A100, or multiple columns like A2:C50. It can even span entire columns like A:A if you want to check every cell in column A. Google Sheets allows you to include your header row in the range; COUNTIF simply skips it if the header doesn’t match your criterion.

The second argument, criterion, is what you’re looking for. This could be a simple value like “red” or 42, a comparison like “>100” or “<=50", or even a cell reference like B1 that contains the value you're searching for. Google Sheets is forgiving about quotes: you can use them or leave them off for numbers, but text and comparisons require quotes.

Here’s a concrete example. Suppose you have a list of sales in cells A2:A50. To count how many sales exceeded 500 dollars, you’d use =COUNTIF(A2:A50,”>500″). Google Sheets checks every cell in the range and counts the ones with values greater than 500.

Another example: if you’re counting how many customers have a last name of “Smith” in column B, and that name appears in multiple cells, you’d use =COUNTIF(B2:B100,”Smith”). The formula returns the exact number of cells containing “Smith”.

One key point: COUNTIF is case-insensitive by default. If you’re searching for “smith”, it will find “Smith”, “SMITH”, and “sMiTh”. If you absolutely need case-sensitive counting, COUNTIF isn’t the right tool, but this usually doesn’t matter in real-world spreadsheets.

You can also use a cell reference as your criterion instead of hardcoding a value. If cell D1 contains the number 100, you can write =COUNTIF(A2:A50,D1) to count cells matching whatever value is in D1. This is powerful because changing D1 instantly updates your count without editing the formula.

Counting Text Values: Exact and Partial Matches

COUNTIF excels at finding text values in your data. The simplest approach is exact matching, where you specify the exact text you want to count.

If you have a list of city names and want to count how many times “Denver” appears, use =COUNTIF(A2:A100,”Denver”). This counts every cell containing exactly “Denver” and nothing else. Cells with ” Denver” (with a space) or “denver” (different case) are counted too, since COUNTIF is case-insensitive, but “Denver, CO” would not match because it contains extra text.

Partial matches are handled with wildcards. The asterisk (*) stands for any number of characters, and the question mark (?) stands for a single character. If you want to count cells that contain “Denver” anywhere within them, use =COUNTIF(A2:A100,”*Denver*”). This matches “Denver”, “Denver, CO”, “Greater Denver Area”, and anything else with Denver in it.

The pattern =COUNTIF(A2:A100,”Denver*”) counts cells that start with Denver. This is useful if you’re looking for variations like “Denver International”, “Denver Tech Center”, and similar entries that all begin with the same word. The asterisk at the end matches anything that follows.

Using =COUNTIF(A2:A100,”*Denver”) counts cells that end with Denver. This is less common but useful in specific scenarios, like counting entries with Denver as the last word.

The question mark wildcard matches exactly one character. =COUNTIF(A2:A100,”smit?”) counts “smith”, “smits”, “smite”, and any other 5-letter word starting with “smit”. Each question mark represents exactly one character, so =COUNTIF(A2:A100,”smit??”) would match 6-letter variations.

If your data contains actual asterisks or question marks that you want to match literally (not as wildcards), precede them with a tilde (~). To count cells containing a literal asterisk, use =COUNTIF(A2:A100,”~*”). The tilde tells Google Sheets to treat the following character as literal text, not a wildcard.

Counting Numbers: Comparisons and Ranges

COUNTIF becomes especially powerful when counting numbers with comparison operators. Instead of looking for exact matches, you can count cells that are greater than, less than, equal to, or within a specific range of values.

The comparison operators are straightforward: > means greater than, < means less than, >= means greater than or equal, <= means less than or equal, and = means exactly equal. Remember to put these inside quotes as part of your criterion.

Suppose you have sales figures in column A and want to count sales over 1000 dollars. Use =COUNTIF(A2:A100,”>1000″). Google Sheets checks every cell and counts those with values strictly greater than 1000. A sale of exactly 1000 would not be counted; only values above 1000 are counted.

If you want to include 1000 in your count, use =COUNTIF(A2:A100,”>=1000″). Now sales of 1000 and above are all counted together.

Counting cells less than a specific value works the same way. =COUNTIF(B2:B50,”<50") counts how many cells in B2:B50 contain values less than 50. This is useful for identifying underperforming metrics or items below a threshold.

Equal comparisons with numbers use the = operator. =COUNTIF(C2:C20,”=100″) counts cells exactly equal to 100. Interestingly, you can also write this as =COUNTIF(C2:C20,100) without the quotes and equals sign; Google Sheets assumes you mean an exact match for numbers.

Not equal is handled differently. There’s no != operator in COUNTIF, but you can work around it. If you want to count cells NOT equal to a specific value, subtract your result from the total count: =COUNTA(A2:A100)-COUNTIF(A2:A100,”=100″). This counts all non-empty cells, then subtracts the count of cells equal to 100, leaving you with the count of cells that don’t equal 100.

Combining comparisons to find values within a range requires COUNTIFS instead of COUNTIF (which you’ll learn about next). But if you need to count a specific range using COUNTIF alone, you can subtract one from another. To count sales between 500 and 1000, use =COUNTIF(A2:A100,”<=1000")-COUNTIF(A2:A100,"<500"). This counts all cells up to 1000, then subtracts those below 500, leaving only the values in between.

Wildcards and Special Patterns

Beyond basic text matching and number comparisons, COUNTIF’s wildcard system allows you to find patterns within your data. Mastering wildcards makes you capable of answering surprisingly complex questions about your data without writing overly complicated formulas.

The asterisk wildcard (*) represents any sequence of characters, including zero characters. =COUNTIF(A2:A100,”h*o”) matches any text starting with “h” and ending with “o”. It would match “hello”, “hullo”, “hello”, “halo”, and even “hoo”. The characters in the middle can be anything.

This is particularly useful for counting email addresses from a specific domain. If you have a list of email addresses and want to count how many are from the gmail.com domain, use =COUNTIF(A2:A100,”*@gmail.com”). Every cell ending with “@gmail.com” gets counted, regardless of the username portion.

The question mark wildcard (?) represents exactly one character, and you can use multiple question marks in a row. =COUNTIF(A2:A100,”???-??-????”) counts cells matching the pattern of a Social Security Number (three digits, hyphen, two digits, hyphen, four digits). Each question mark is one character position.

Combining multiple wildcards gives you even more control. =COUNTIF(A2:A100,”*-*-*”) counts any cell containing at least two hyphens. =COUNTIF(A2:A100,”?l*”) counts cells where the second character is “l”, followed by anything.

The tilde (~) is an escape character for when your data contains actual asterisks or question marks. If you’re working with data that includes product codes like “ITEM*001” (where the asterisk is a literal character, not a wildcard), and you want to count exactly those items, use =COUNTIF(A2:A100,”ITEM~*001″). The tilde tells Google Sheets to treat the asterisk as a literal character, not a wildcard.

Escaping is rare in typical spreadsheets, but it’s good to know when you encounter unusual data formats or product identifiers that include special characters.

COUNTIF vs. COUNTIFS: When You Need Multiple Criteria

COUNTIF handles one criterion. When you need to count cells that match multiple criteria simultaneously, COUNTIFS is the tool for the job. Understanding when to use each formula is crucial for efficient spreadsheet work.

COUNTIF syntax is =COUNTIF(range, criterion). COUNTIFS syntax extends this: =COUNTIFS(range1, criterion1, range2, criterion2, …). You can include as many range-criterion pairs as you need.

Here’s a practical example showing the difference. You have sales data with columns for Salesperson (A), Region (B), and Amount (C). To count sales over 1000 dollars using COUNTIF: =COUNTIF(C2:C100,”>1000″). This counts all sales over 1000, regardless of who made them or where they’re from.

But what if you want to count sales over 1000 dollars made specifically by John in the West region? COUNTIF can’t do this because you need to match three conditions: the salesperson is John, the region is West, and the amount is over 1000. This is where COUNTIFS shines: =COUNTIFS(A2:A100,”John”,B2:B100,”West”,C2:C100,”>1000″).

The formula checks row by row. Only rows where column A equals “John”, column B equals “West”, and column C is greater than 1000 are counted. Rows that don’t meet all three conditions are skipped. This is the key difference from COUNTIF: COUNTIFS uses AND logic, meaning all criteria must be true.

You can mix exact matches and comparisons in COUNTIFS. =COUNTIFS(A2:A100,”Smith”,B2:B100,”>=100″) counts rows where column A is “Smith” AND column B is 100 or greater. You can add as many criteria as you need, though formulas become harder to read beyond 4 or 5 criteria.

COUNTIFS also accepts wildcards just like COUNTIF. =COUNTIFS(A2:A100,”*Smith*”,B2:B100,”>1000″) counts rows where column A contains “Smith” (anywhere in the text) and column B exceeds 1000.

One limitation: COUNTIFS uses AND logic only. If you need OR logic (count rows matching criterion1 OR criterion2), you’ll need to use separate COUNTIF formulas and add them together: =COUNTIF(A2:A100,”John”)+COUNTIF(A2:A100,”Mary”) counts rows where the person is either John or Mary.

Counting Data from Multiple Sheets

Many spreadsheets contain data split across multiple sheets. You might have individual sheets for each month’s sales, or separate sheets for each region. COUNTIF can reach across sheets and combine counts from all of them.

The syntax includes the sheet name: =COUNTIF(SheetName!A2:A100,”Apple”). Replace “SheetName” with your actual sheet name. If your sheet name contains spaces or special characters, wrap it in single quotes: =COUNTIF(‘Sales Data’!A2:A100,”Apple”).

Counting across multiple sheets requires adding multiple COUNTIF formulas together. If you have three sheets named “January”, “February”, and “March”, and you want to count a value across all three, use =COUNTIF(January!A2:A100,”Apple”)+COUNTIF(February!A2:A100,”Apple”)+COUNTIF(March!A2:A100,”Apple”).

This approach works well when you have a few sheets. For very large numbers of sheets, writing dozens of formulas becomes unwieldy. In those cases, consider consolidating your data into a single sheet first, then using a single COUNTIF formula.

Another option is to use IMPORTRANGE to pull data from multiple sheets into one location, then count that consolidated data. This is more complex to set up but creates a more maintainable solution for ongoing analysis.

Finding Duplicates with COUNTIF and Conditional Formatting

One of the most practical uses for COUNTIF is identifying duplicate entries in your data. By combining COUNTIF with conditional formatting, you can instantly highlight any value that appears more than once.

Start by selecting the range you want to check for duplicates. Open the Format menu and click “Conditional formatting.” Under Format rules, choose “Custom formula is” and enter =COUNTIF($A$2:$A$100,A2)>1.

This formula counts how many times each value appears in your range. If the count is greater than 1, the cell is highlighted. The dollar signs create absolute references for the range (stays constant) while keeping the cell reference relative (changes for each row as the formula is applied).

For multi-column duplicates, the formula is slightly different. If you’re checking whether entire rows are duplicated based on columns A and B, use =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1. This counts rows matching both column A and column B. If the count exceeds 1, those columns contain duplicate data.

Once you’ve applied the conditional formatting, every duplicate appears highlighted in your sheet. You can choose your highlight color when setting up the rule. Red is traditional for duplicates, but any color works. This visual approach makes duplicates impossible to miss when reviewing your data.

This is especially useful before using the remove duplicates in Google Sheets feature, as it lets you inspect duplicates first and understand which ones you want to keep.

Real-World Use Cases for COUNTIF

Understanding COUNTIF syntax is one thing; knowing when and how to apply it in real situations is another. Here are four common scenarios where COUNTIF solves actual business problems.

Attendance Tracking: You manage attendance for a team and want to know how many people were present on a specific day or how many absences each person has accumulated. If your data has columns for Name, Date, and Status (Present or Absent), you can count absences using =COUNTIF(C2:C100,”Absent”). To count how many times a specific person was absent, use =COUNTIFS(A2:A100,”John”,C2:C100,”Absent”). This tells managers instantly who has the most absences.

Inventory Management: You track inventory with columns for Item, Quantity, and Reorder Level. To count items below the reorder level, use =COUNTIF(B2:B100,”<50") (assuming 50 is your threshold). If items below threshold need ordering, this count tells you how many purchase orders to generate. For more complex scenarios with multiple warehouses, =COUNTIFS(A2:A100,”*Ball*”,B2:B100,”<30") counts items containing “Ball” in the name with fewer than 30 units.

Survey Analysis: You’ve collected survey responses with columns for Question, Response, and Count. To analyze how many people selected “Very Satisfied” versus other options, use =COUNTIF(B2:B100,”Very Satisfied”). This instantly gives you the distribution of responses. Comparing multiple COUNTIF formulas shows which responses were most popular: =COUNTIF(B2:B100,”Very Satisfied”), =COUNTIF(B2:B100,”Satisfied”), and so on.

Grade Distribution: You have a list of student grades and want to see how many students achieved each letter grade. Using =COUNTIF(A2:A50,”A”), =COUNTIF(A2:A50,”B”), and similar formulas, you instantly see how many students earned each grade. This helps identify whether your grading distribution is reasonable or if you need to adjust your expectations.

Frequently Asked Questions

COUNTIF is one of those formulas that becomes more valuable the more you use it. Once you master the basics, you’ll discover new applications in nearly every spreadsheet you create. Whether you’re analyzing data, tracking metrics, or cleaning duplicates, COUNTIF delivers quick answers to common questions. For related formula skills, explore our guides on SUMIF in Google Sheets, IF formula in Google Sheets, and VLOOKUP in Google Sheets. Combined, these formulas form the foundation of powerful data analysis in Google Sheets.