OmniDocHow to count how many times data appears in Excel
Using a COUNTIF formula that extends the range, you can number the repeated values row by row, marking how many times each data occurs. This article explains how to write formulas, filling methods, how to filter for the Nth time, and where mistakes are likely to occur when checking results.
Key points of this article
- Enter =COUNTIF($A$2:A2,A2) in the auxiliary column and fill it down. The result of 1 is the first occurrence, and 2 and above are the second and third occurrences.
- $A$2 locks the starting point, and the end point of the range A2 moves down with the row, so the statistics are the number of occurrences of "up to the current row", which is different from the total number of statistics.
- When judging multiple columns of conditions at the same time, use COUNTIFS instead and expand the condition area of each column row by row.
- Blank lines, textual numbers, overlong numbers and wildcard characters may cause errors in the results. Please check randomly after filling.
Finding out how many times a certain data appears in a table is a very common need when organizing lists, orders and logs. For example, if you want to see how many times the same customer has placed an order, or how many times the same number appears in the ledger, you need to label each record with a serial number line by line. Many people will first think of counting the total number, but the total number can only tell you how many times this value appears in total, and it is impossible to distinguish which row is the first time and which row is the second time.
This article talks about how to use Excel tables to count how many times a piece of data appears. The core method is a COUNTIF formula that expands along the rows, filling in once to number the entire column. After reading, you can get the occurrence sequence number of each record in the auxiliary column, and use it to filter out all rows with "second occurrence" or "first occurrence".
Below is a judgment table first, and then the operating steps and verification methods.
How to mark how many times it appears? One sentence conclusion
Direct answer: judge based on only one column, use COUNTIF with the area of "lock the starting point and expand the end point along the way"; when you need to meet the conditions of two columns at the same time, use COUNTIFS instead.
| your situation | Recommended practices | reason |
|---|---|---|
| Judge only by one column, such as the same name | =COUNTIF($A$2:A2,A2) | The result is the number of occurrences up to the current row, directly |
| Duplication is only considered if two columns are the same at the same time, such as name plus date. | =COUNTIFS($A$2:A2,A2,$B$2:B2,B2) | Each column condition is expanded along with the row, and both columns must match |
| Just want to see which values are repeated | Number first, then filter rows with serial numbers greater than 1 | The sequence number greater than 1 is the record starting from the second time |
| I just want to know how many times the value appears in total | =COUNTIF($A$2:$A$100,A2) | This is the total number of statistics, which is different from the number of occurrences |
| Data in PDF table | First export to Excel or do OCR, and then set the formula | Formulas can only work on text and numbers in cells |
Before you start: Confirm these points first
First confirm whether the data is standardized. The formula compares based on the content of the cell. If the same name sometimes has spaces and sometimes does not, Excel will treat them as two values. Before you start, you can use filter to take a look at the value of the column and write it in a unified way.
Confirm header row and data starting point. The above formula assumes the data starts at A2 and A1 is the title. If your data starts from row 5, $A$2 in the formula must be changed to $A$5, and the starting point should be aligned with the first piece of data, otherwise the result will be under- or over-counted.
Watch out for blank cells and long numbers. Blank rows may be counted as a value in COUNTIF. In addition, for numbers that exceed 15 digits such as ID numbers and bank card numbers, Excel only retains the first 15 digits of valid precision, and different numbers in the last few digits may be judged to be the same. This type of column should be converted to text first and then counted.
Watch out for wildcards. If the conditions of COUNTIF contain asterisks, question marks or tildes, they will be treated as wildcards. For example, "A*" will match all content starting with A. If there are these symbols in the data before numbering, the results may deviate from expectations and require manual inspection.
Data is converted first when in PDF. If the table is originally a PDF, you can use the PDF editor of the OmniDoc desktop client to export it to Excel; the scanned PDF must first use OCR to recognize the text. There may be typos in the recognition results. After importing the form, you must check the key data one by one and then perform statistics.
Steps to number rows in Excel
- Open the table, leave a blank column to the right of the data column, and write a name such as "Occurrence Order" in the title of the first row.
- Enter the formula in the second row (B2) of the auxiliary column: =COUNTIF($A$2:A2,A2), and press Enter to confirm. A2 here is the cell where the value to be counted is located.
- Look at the results of B2. It should be 1, indicating that the value A2 occurs for the first time in the current range.
- Drag the fill handle in the lower right corner of B2 to fill down to the last piece of data, and a serial number will be automatically generated for each row.
- Check several rows of results: the row with the second occurrence of the same value should show 2, the third time with 3, and so on.
- If there may be blank rows in the data, change the formula to =IF(A2="","",COUNTIF($A$2:A2,A2)), and the serial number will not be displayed on the blank rows.
- When you need to judge based on two column conditions, change the formula to =COUNTIFS($A$2:A2,A2,$B$2:B2,B2), and drag to fill in the same way.
- When filtering is required, enable filtering on the auxiliary column and select the value 2 to see all records that appear for the second time.
Check each item after completion
After the numbering is completed, do not use the results directly. It is recommended to check according to the following items.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Whether the first occurrence is 1 | Randomly select several new values, and the corresponding sequence number should be 1 | Check whether the starting point is written as $A$1 or whether the data starts from other lines |
| Is the formula filled correctly? | Look at the formula for the last row, it should refer to column A of the same row | If the reference is misplaced, re-enter the formula on the first line and fill it in again. |
| Blank lines and header lines | Empty rows appear blank and titles are not counted. | Use IF to determine empty rows, or confirm that the starting point starts from the first row of data |
| Whether text and numbers are mixed | Can the same number match in different rows? | Unify both sides into text or numbers and recalculate |
| Is it consistent with manual counting? | Use filter to see the number of rows for a certain value and compare it with the large serial number | Check for whitespace, case (COUNTIF is not case-sensitive), and wildcards when inconsistent |
📊 Core comparison
According to different statistical purposes, there are three common ways of writing. The main differences between the three are whether the area is expanded and how many columns there are in the condition column.
| statistical purposes | Recommended writing method | Questions suitable to be answered |
|---|---|---|
| order up to current row | Extended range COUNTIF, such as =COUNTIF($A$2:A2,A2) | How many times does this value appear in this line? Quickly filter out first or repeated records |
| The total number of the entire table | Fixed area COUNTIF, such as =COUNTIF($A$2:$A$100,A2) | How many times does this value appear in the entire table? |
| The order of multiple column combinations | Extended area COUNTIFS, such as =COUNTIFS($A$2:A2,A2,$B$2:B2,B2) | How many times does the same person appear on the same day? |
Think clearly about the questions you want to answer first, and then choose the corresponding writing method to avoid mistaking the total for the order.
❓ Frequently Asked Questions (FAQ)
1. Why is the formula result in the first row 1, but the result does not change when the same value appears later?
Most likely, the lock of $A$2 is not written correctly, or the area is not expanded along with the filling. Change the formula back to =COUNTIF($A$2:A2,A2), make sure the end point is a relative reference to A2, and fill it in again.
2. I want to count how many times a piece of data appears in the table, but does it count as the same value in different cases?
COUNTIF is not case sensitive, "Apple" and "APPLE" will be counted as the same value. If you need to be case-sensitive and use more complex formulas, it is recommended to write the data in a unified way before statistics.
3. Can you directly delete all rows that appear for the second time and later?
You can number first, then filter the rows with serial numbers greater than 1 for inspection, and then delete them after confirming they are correct. Before deleting directly, try to back up the original table to avoid accidentally deleting useful records.
4. When there is a lot of data, will the formula be very slow?
Each row of COUNTIF in the extended area counts all previous rows, and the calculation will slow down when the amount of data is large. Thousands of daily rows usually do not affect usage. If the amount of data is larger, you can consider filtering or splitting into tables first.
📝 Summary
To count how many times a piece of data appears in Excel, the key is to number it row by row using the COUNTIF formula that locks the starting point and expands the end point along the row. After filling, the result of 1 is the first occurrence. The larger the number, the later it appears. After numbering, remember to check blank lines, text numbers and wildcards, so that the results of counting the number of occurrences of a data in the table are reliable.
