How do you count how many times a value appears in Excel?
Last updated February 17, 2026
In Excel, you often work with lists: customers, orders, statuses, products or error messages. Then you want to know how often a certain value appears. For example, how many times “Paid” appears in a column or how often a specific employee appears in a schedule.
This seems simple, but the way you count determines whether your outcome is correct. Excel offers several functions to do this reliably and efficiently. Below we explain them clearly, including when to use which method.
The basics: using NUMBER.ALS
Want to know how often one specific value appears in a range? Then use NUMBER.ALS.
The structure of the formula is:
=AANTAL.ALS(range; criteria)
Range is the column or range in which you want to count. Criteria is the value you are looking for.
This is how you apply NUMBER.ALS
- Select the cell in which you want to display the result. This is usually next to or below your dataset so that you keep an overview.
- Type the formula, for example:
=AANTAL.ALS(A2:A100; "Paid")
In this example, Excel counts how many times “Paid” appears in column A. - Press Enter. You will immediately see the number of matches in your dataset.
If you are working with numbers or a reference to another cell as a criterion, you can also do this. For example:=NUMBER.IF(A2:A100;D1)
Here Excel counts how many times the value from cell D1 appears.
A common mistake is that spaces or hidden characters in your data cause anomalous counts. Check this if an outcome does not seem logical.
Counting with multiple terms: NUMBERS.ALS
Sometimes one criterion is not enough. For example, you want to know how often “Paid” occurs AND only in 2025 AND within Department of Sales. Then you use NUMBERS.IF.
The structure is:
=A NUMBERS.IF(range1; criteria1; range2; criteria2; ...)
This is how you use NUMBERS.ALS
- For each condition, determine which range belongs to it. Each condition must be associated with its own column.
- Build your formula step by step. For example:
=AANTALLEN.ALS(A2:A100;"Betaald";B2:B100;"2025")
Here, Excel counts only the rules that satisfy both conditions. - Check that all ranges are exactly the same size. If one range is longer than another, you will get errors or incorrect counts.
This feature is particularly suitable for reports and management overviews where multiple filters are needed simultaneously.
Complex counting with matrix formulas
In some situations, standard functions don't work out. For example, when you want extra control over logic or when you are combining multiple calculations.
Then you can work with the formula:
=SOM(IF(range=criteria;1;0))
What exactly is happening here?
First, Excel checks per cell whether the value matches the criterion. If so, that cell gets a 1. If not, the cell gets a 0. Then the function SOM adds up all the ones. The total is the number of matches.
In older Excel versions, you close this formula with Ctrl+Shift+Enter. In modern versions with dynamic matrices, this is usually no longer necessary.
Use this method only if NUMBER.ALS or NUMBER.ALS is not sufficient. Matrix formulas make a worksheet less clear for colleagues.
Visually checking with conditional formatting
Sometimes you want to know not only how many times a value appears, but also to see where those values are. Then conditional formatting is a practical addition.
Here's how to set conditional formatting
- Select the range you want to check. This is usually the column in which you are also counting.
- Go to Start and select Conditional Formatting. Then select New Rule.
- Choose the type of rule you want to use, such as a formula that determines which cells are formatted.
- Enter your criterion and choose a formatting, such as a striking color. Confirm with OK.
This marks all matching values visually. The count itself does not change, but your checking becomes easier.
Practical concerns for reliable counts
Preferably work with Excel tables via Ctrl + T. Your formulas will automatically grow with you as you add new lines. This prevents new data from falling out of your scope.
Use clear column names or named ranges. This makes your formulas readable and more transferable to colleagues. Check regularly for duplicates. Duplicate entries affect your count and can lead to incorrect conclusions in reports.
Relevance to practice
At Mica IT, we regularly see reports showing incorrect numbers due to minor formula errors or incorrectly defined ranges. Especially when files are shared internally, these discrepancies accumulate quickly.
An accurate count is not a detail. It determines whether decisions are based on correct information. By consciously applying features and keeping your structure neat, you avoid discussions after the fact.
Need help with reliable Excel reports?
Frequently Asked Questions
What is the difference between NUMBER and NUMBER.ALS?
NUMBER only counts cells with numeric values. NUMBER.ALS counts cells that meet a specific criterion, regardless of whether they are text or numbers. If you want to search specifically for a single value, you almost always use NUMBER.ALS. For general counting of numbers, AANTAL is sufficient.
Why isn't my count correct?
This is often due to hidden spaces, different notations or different dates. Also, the range may not be completely selected. Check that all ranges are the same size and that your criteria match the dates exactly. Small differences immediately cause anomalous results.
Can I count case sensitive in Excel?
By default, NUMBER.ALS and NUMBER.ALS are not case sensitive. If you want them to be, you have to work with a combination of EXACT and SOM. This requires a more sophisticated formula and is especially relevant for specific validations.
How do I count unique values?
Do you want to count not the total number, but the number of unique values? Then in modern Excel versions you use the function UNIQUE in combination with NUMBER. For example:=NUMBER(UNIQUE(A2:A100))
Gets the number of unique items in a column.
What is the best method for reporting?
For standard reports, NUMBER.ALS and NUMBER.ALS are the most stable and understandable. Matrix formulas use only when really necessary. Keep your worksheet organized so colleagues understand what is happening.
