How to use Excel counting function COUNTIF: count numbers according to conditions

Key points of this article

COUNTIF (range, condition) counts the number of cells in the area that meet the conditions. The conditions can be text, numbers, comparisons or referenced cells.
COUNT counts only numeric cells, COUNTA counts all non-empty cells, and COUNTIF counts after filtering according to the conditions you specify.
Text conditions can use and ? as wildcard characters. For example, "Beijing" counts content starting with Beijing.
It is recommended to fix the rows and columns in the statistical area and add a dollar sign before the column letters and row numbers so that the range will not shift when the formula is copied downwards.
If the result is incorrect, first check whether there are any extra spaces in the conditional text, and then use the filter results to compare them one by one.

When making tables, we often encounter such problems: a 30-line order table, you need to know how many "completed" orders there are; a score table, you need to count the number of people who have passed. Counting line by line is slow and easy to make mistakes. Excel's COUNTIF function is designed for this type of problem. It can automatically count in a range based on conditions. Many people search for "how to use the excel function countif". In fact, they want to know how to write formulas and how to fill in conditions.

Many people subconsciously use COUNT, but COUNT only counts numerical cells and cannot do anything when encountering text statuses such as "Completed" and "Unpaid". The two functions have different purposes. If you choose the wrong one, the results will be very different. This is also a confusing point in "How to use the countif function in excel".

This article explains the basic writing method of COUNTIF, several common forms of conditions, the use of wildcards, and its differences with COUNT and COUNTA. After reading, you can write your own conditional counting formulas and learn to check whether the results are correct.

How to use COUNTIF? One sentence conclusion

COUNTIF accepts two parameters: statistical area and conditions, and returns the number of cells in the area that meet the conditions, for example =COUNTIF(B2:B31,"Completed"). The conditions are written differently and the formulas are slightly different. The table below corresponds to common situations.

your situation	Recommended practices	reason
Count the number of times a certain text content appears, such as "Completed"	=COUNTIF(area,"Completed")	Text conditions must be placed in English double quotes
Count cells greater or less than a certain number	=COUNTIF(area,">60")	Comparison symbols and numbers are placed in quotation marks
Count content that begins with a certain word or contains a certain word	=COUNTIF(area,"Beijing")or"Beijing*"	The asterisk represents any number of characters
The conditions are written in another cell and often need to be changed.	=COUNTIF(area,E2)or">"&E2	Change the cell content to update the results without changing the formula
Just want to count numbers, not words	Use COUNT instead of COUNTIF	COUNT counts only numeric cells

Before you start: Confirm these points first

Determine the column to be judged. COUNTIF can only count one area. First find the column that stores the basis for judgment, such as the "status" column, rather than the entire table. If you select the wrong area, the result looks normal, but is actually biased.
Distinguish between areas and conditions. The area determines where to look, and the conditions determine what to look for. The two are often mixed together. Writing "where to look for and what to look for" on paper before writing the formula can reduce rework.
Check data format. Numbers are stored as text (there is often a small green triangle in the upper left corner of the cell), and there are spaces before and after the content, which will make content that looks the same unable to match and affect the statistical results.
Don’t count the title line. The statistical area should start from the first piece of data. The header row usually does not meet the conditions, but if the title happens to be the same as the conditions, it will also be miscounted.
Confirm if there are multiple conditions. COUNTIF can only set one condition; to satisfy both conditions, you need to use COUNTIFS, which is another function.

Steps to count numbers based on conditions in Excel

Assume that column B is the order status, and there are 30 records in rows 2 to 31. Now we want to count the number of "completed" orders.

Select a blank cell, such as F2, to display statistical results.
Enter the equal sign and function name, written as =COUNTIF(.
Use the mouse to drag the statistical area B2:B31, or directly enter it manually to search only in this column.
Enter an English comma, then enter the condition, and add English double quotes to the text condition, such as "Completed". Please make sure to use English half-width symbols. Chinese symbols will cause the formula to report an error.
Enter the right bracket and press Enter, the cell will display the number that meets the condition.
If you want to change the statistical content, you can write the condition as a cell reference, such as =COUNTIF (fixed area of column B, E2), enter the text to be counted in E2, and modify E2 to get new results.
When you need to copy the formula to other cells, add a fixed symbol to the range: add a dollar sign before the column letter and row number, so that the range from rows 2 to 31 of column B remains unchanged during copying.
Try two or three conditions each once, and confirm that the number returned is consistent with your manual counting results.

Examples of commonly used conditional writing:

"Completed": Cells whose statistics content is exactly equal to "Completed".
">=60": Count numbers greater than or equal to 60.
"<>Completed": Count cells that are not equal to "Completed".
"Zhang": counts text starting with "Zhang", can represent any number of characters.
"?Class": counts the content of two characters plus "Class", ? represents only one character.

Check each item after completion

After the formula calculates the results, it is recommended to check the following aspects one by one, especially when the amount of data is large.

Check items	How to see	What to do if there is a problem
Whether the result is 0	There is obviously content in the data that meets the conditions, but the result shows 0	Check whether the condition text is completely consistent with the cell, remove excess spaces, and unify full-width and half-width symbols
Is the area complete?	Select the range in the formula to see if it contains all data rows and does not include titles	Adjust the area range and confirm that the rows and columns have been fixed
Is the comparison valid?	Change the number in the condition to another value and observe whether the result changes accordingly	Check whether there are extra characters between symbols and numbers, and whether the quotation marks are paired
Compare with filter results	Use filtering on this column to see if the number of filtered rows is consistent with the formula result.	In case of inconsistency, check line by line which content was missed or miscounted.
Whether numbers and text are mixed	There is a green triangle in the upper left corner of the number column, or the numbers are aligned to the left	Convert literal numbers into numerical values and re-calculate them

If the data comes from PDF or scanned documents, the table contents need to be sorted into Excel before statistics. There may be errors or omissions in the automatically recognized text. Before counting, you should check it against the original text to avoid inaccurate figures.

📊 Core comparison

There are several counting-related functions in Excel. They count different objects and are often used interchangeably.

function	What statistics	Typical writing
COUNT	Only count cells	=COUNT(C2:C31)
COUNTA	Count all non-empty cells, including text and numbers.	=COUNTA(B2:B31)
COUNTIF	Only count cells that meet the specified conditions	=COUNTIF(B2:B31,"Completed")

Note that COUNTA will also count cells where the formula returns empty text, so the result may be more "non-empty" than what appears. If you only want to count cells with specified content, COUNTIF is usually more appropriate.

❓ Frequently Asked Questions (FAQ)

1. Can COUNTIF count multiple conditions?

COUNTIF can only set one condition. To satisfy two conditions at the same time, such as "the department is sales and the amount is greater than 1000", you can use COUNTIFS, which is written by writing each area and corresponding condition in turn.

2. Why is the result still 0 even though the condition contains text?

Common reasons are that the cell content contains extra spaces, or full-width characters are used, or numbers may be saved as text. You can click on the cell to view the edit bar, check the content, and then make unified changes and recalculate.

3. How to count the number of blank cells?

Use =COUNTIF(range,"=") to count truly empty cells. Using =COUNTIF(range,"") will count empty cells and cells whose formula returns empty text together. The results of the two may be different. You should confirm which one is needed before using it.

4. Can COUNTIF count by color?

COUNTIF itself does not recognize cell colors. A common practice is to filter by color first, and then check the number of filtered rows; if you need to use it for a long time, you can add a column next to it, mark the status with text, and then use COUNTIF to count this column.

📝 Summary

The core of COUNTIF is "statistical area plus conditions". When using it, first distinguish the area and conditions, and then pay attention to quotation marks, wildcards and fixed rows and columns. After the statistics are completed, use the screening results or manual spot checks to compare them once, and the results will be more reliable.

Open OmniDoc and work with your spreadsheet file