COUNTIF is a Google Sheets function that counts the number of cells in a range that meet a single condition you specify. It’s useful for tallying responses, tracking inventory, or checking how many values fall above a threshold. You’ll need a Google account and a sheet with data already entered.
What You’ll Need
- A Google account (free)
- A Google Sheets document with data in a column or row
- A clear idea of the condition you want to count (e.g., “greater than 10” or “equals ‘Yes'”)
Step-by-Step
- Open your Google Sheet in a web browser. Go to sheets.google.com and open the file containing your data.
- Select an empty cell where you want the count to appear. Click once on that cell to make it active.
- Type the formula starting with an equals sign:
=COUNTIF(. As you type, Google Sheets will show a small help box explaining the arguments. - Specify the range. This is the group of cells you want to examine. You can type it manually (e.g.,
A2:A100) or click and drag over the cells after typing the opening parenthesis. A range likeA2:A100means column A, rows 2 through 100. - Add a comma after the range to separate the arguments.
- Enter the condition. This is the criteria that determines which cells to count. It can be a number, text, or an expression. For example:
- Number:
10counts cells equal to 10. - Text:
"Yes"counts cells that contain the exact text “Yes” (including quotes). - Comparison:
">10"counts cells greater than 10. - Cell reference:
B1counts cells equal to whatever value is in B1.
- Number:
- Close the parentheses and press Enter. The cell will immediately show the count.
- Check the result. If you get an unexpected number, review the range and condition for typos.
Common Mistakes
- Forgetting quotes around text: Text conditions must be in double quotes, like
"Approved". Without quotes, Sheets treats it as a named range and returns an error. - Using wrong comparison operators: For “greater than or equal to”, you need
">="inside quotes. Writing>=10without quotes will cause a parse error. - Mixing data types: If your range contains numbers formatted as text (e.g., with a green triangle), COUNTIF may not match them correctly. Ensure numbers are actual numbers.
- Including the header in the range: If your header is text like “Name”, and your condition is a number, it won’t matter. But if your condition is text, the header might be counted unintentionally. Start your range at the first data row.
- Using COUNTIF for multiple conditions: COUNTIF only handles one condition. For multiple criteria, use COUNTIFS instead.
Tips
- Use wildcards for partial matches: The asterisk (*) matches any sequence of characters, and the question mark (?) matches any single character. For example,
"*apple*"counts cells containing the word “apple” anywhere. - Reference a cell for the condition: Instead of typing a value directly, use a cell reference like
=COUNTIF(A2:A100, B1). Then you can change B1 without editing the formula. - Count blank or non-blank cells: Use
""to count empty cells (e.g.,=COUNTIF(A2:A100, "")) and"<>"to count non-empty cells. - Combine with other functions: You can nest COUNTIF inside SUM, AVERAGE, or IF to build more complex logic. For example,
=IF(COUNTIF(A2:A100, "Fail")>0, "Review", "OK"). - Use COUNTIFS for multiple conditions: If you need to count cells that meet several criteria, use COUNTIFS with paired ranges and conditions, like
=COUNTIFS(A2:A100, "Yes", B2:B100, "Done").
Troubleshooting
Formula returns an error like #NAME? or #VALUE!
This usually means the formula is misspelled or the condition is not formatted correctly. Check that you typed COUNTIF correctly and that text conditions are in double quotes. Also ensure there is no extra space before the equals sign.
The count is zero when it should be more
Check for invisible characters or extra spaces in your data. A cell that looks like “Yes” might actually be “Yes ” with a trailing space. Use the TRIM function to clean data first, or use a wildcard like "*Yes*".
The count changes when I edit a cell
That’s normal. COUNTIF recalculates automatically. If it doesn’t, go to File > Settings and check that calculation is set to “On change”.
COUNTIF is case-sensitive but I need it to ignore case
COUNTIF is not case-sensitive by default (it treats “yes” and “YES” the same). If you need case-sensitive matching, use a helper column with EXACT or use ARRAYFORMULA with SUMPRODUCT.
Frequently Asked Questions
Can COUNTIF count cells that contain specific text anywhere?
Yes. Use wildcards: =COUNTIF(A2:A100, "*text*") counts cells that have “text” anywhere in them.
How do I count cells that are not equal to a value?
Use the <> operator: =COUNTIF(A2:A100, "<>Done") counts all cells that are not exactly “Done”.
Can I use COUNTIF with dates?
Yes. For a specific date, use =COUNTIF(A2:A100, DATE(2024,1,1)) or reference a cell. For dates after a certain day, use =">"&DATE(2024,1,1).
Does COUNTIF count blank cells?
Yes, if you use "" as the condition. But note that cells with a formula that returns an empty string are counted as blank.
What is the difference between COUNTIF and COUNTIFS?
COUNTIF counts cells that meet one condition. COUNTIFS counts cells that meet multiple conditions across different ranges. For example, count orders that are “Paid” and “Shipped”.
Can I use COUNTIF across multiple sheets?
Yes, but you need to reference each sheet separately or use a helper column. For example, =COUNTIF(Sheet1!A2:A100, "Yes") + COUNTIF(Sheet2!A2:A100, "Yes").
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- How to Turn Off Shutter Sound on iPhone
- VPN Setup on iPhone SE: Step-by-Step Guide
- Fix Internet Not Working on Windows 11
- Fix Right Click Not Working in Firefox
- Compare Two Columns with Comma Separated Values in Excel
- How to Limit Ad Tracking on iPhone Apps