Excel COUNT Function: Syntax, Examples, and How It Differs From COUNTA, COUNTIF, and COUNTBLANK

The Excel COUNT function is the workhorse you reach for when you want a clean tally of numbers — and only numbers — in a range. Type =COUNT(A1:A100) and Excel walks every cell in that range and counts how many of them contain a numeric value. Text, blanks, errors, and logical TRUE/FALSE values are all ignored.

That selective behavior is exactly what makes COUNT useful — and also exactly what confuses people the first time they switch from it to COUNTA, COUNTIF, or COUNTBLANK. This guide breaks down the syntax, walks through eight real examples, and untangles the difference between COUNT and its four cousins.

By the end you will know which function to use for which job, how to fix the most common errors, and how to combine COUNT with other functions for surprisingly powerful summaries. We will start with the basic syntax, then move into examples, comparisons, and troubleshooting.

The syntax of COUNT is straightforward: =COUNT(value1, [value2], ...). The first argument is required, and you can pass up to 255 additional arguments. Each argument can be a single cell, a range, a named range, a constant, or another formula that returns a number.

Excel counts only cells that contain numbers — that includes dates, times, currency values, and percentages, all of which are stored internally as numbers. Cells with text, blanks, formula errors (#N/A, #DIV/0!, etc.), and the logical values TRUE and FALSE are skipped. The function never returns an error of its own — it always returns an integer between 0 and the total cell count in your range.

Example 1: Count Numbers in a Single Column

This is the simplest case. Suppose column A holds 500 cells, some with sales figures and some with text labels. The formula =COUNT(A1:A500) returns the number of cells that contain numeric sales values, ignoring the labels and any blank cells. If 312 of the 500 cells are numbers, the result is 312.

This is your go-to formula when you need to confirm how many rows of numeric data you actually have, regardless of how many rows the range covers. It is especially handy for data-quality checks before running aggregations like SUM or AVERAGE.

Example 2: Count Across Multiple Ranges

COUNT accepts multiple arguments, so you can count across non-adjacent ranges in one formula. =COUNT(A1:A100, C1:C100, E1:E100) totals the numeric cells in all three columns. Excel adds the individual counts together — there is no double-counting because each cell appears in only one range.

This pattern is useful for quarterly or monthly summaries where the data lives in separate columns rather than one continuous block. It also works with whole rows: =COUNT(1:1) counts every numeric cell in row 1.

Example 3: Count Dates in a Range

Because Excel stores dates as serial numbers under the hood (1 = January 1, 1900 on Windows), COUNT happily tallies them. =COUNT(B2:B500) on a column of order dates returns the number of dates entered — but watch out: dates typed as text (like "Jan 5, 2026" with no automatic conversion) are not counted.

To check whether a "date" is actually a date, look at the cell alignment in the default General format: real dates align right, text dates align left. If COUNT undercounts, use the DATEVALUE function to convert text to real dates first, or see our Excel date functions guide.

Example 4: Count Cells That Pass a Logical Test

COUNT does not accept criteria — for that you want COUNTIF. But you can simulate criteria by wrapping COUNT around an IF that returns numbers for matches and nothing for non-matches. =COUNT(IF(A2:A100>1000, A2:A100)) entered with Ctrl+Shift+Enter (or as a dynamic array in Excel 365) returns the count of values above 1000.

This is a legacy pattern from before COUNTIF existed. In modern Excel you should reach for COUNTIF, COUNTIFS, or SUMPRODUCT — they are cleaner and faster. The trick remains useful when you need to nest counting inside a more complex calculation.

Example 5: Count Numbers Returned by Another Formula

COUNT works on formula results, not just typed values. If column D contains =VLOOKUP(...) formulas that return either a number or #N/A, =COUNT(D2:D500) returns the number of successful lookups — errors are skipped automatically.

This is a useful sanity check for any pipeline that uses lookups: a sudden drop in the COUNT result tells you a wave of lookups is failing without needing to scroll through hundreds of rows looking for red error cells. For more on handling lookup errors, see our Excel VLOOKUP function guide.

Example 6: Count With Constant Arguments

You can pass literal values directly: =COUNT(5, 10, "fifteen", TRUE, 20) returns 3 because only the three numeric literals are counted. The text "fifteen" and the logical TRUE are skipped. This rarely matters in everyday work but it explains why a formula with mixed inputs can return surprising values.

The exception worth knowing: numeric strings like "10" passed as direct arguments are converted and counted. But the same "10" sitting inside a cell as text is not counted when the cell is referenced. The conversion only happens for literal arguments, not range references.

Example 7: COUNT Combined With Other Functions

One of the most common patterns is calculating the average using SUM and COUNT separately so you can audit each step: =SUM(A1:A100)/COUNT(A1:A100). This is exactly what AVERAGE does internally, but writing it out helps when you need to debug a strange-looking mean.

Another pattern: =COUNT(A1:A100)/COUNTA(A1:A100) gives you the fraction of cells that hold numbers versus all non-empty cells. Multiply by 100 for a percentage. Useful for data-quality dashboards where you track how much of your input is properly typed.

Example 8: COUNT on Filtered Ranges

COUNT counts hidden cells, including those hidden by filters or by manually hidden rows. To count only visible cells, use SUBTOTAL with function number 2 (which is the COUNT operation): =SUBTOTAL(2, A1:A100). This returns the count of visible numeric cells only — and updates dynamically as the filter changes.

If you do a lot of work with filtered tables, SUBTOTAL is often the better choice. The newer AGGREGATE function (function number 2 as well) gives you even more control, including skipping errors automatically. See our Excel SUBTOTAL function guide for the full breakdown.

COUNT vs COUNTA: The Key Difference

COUNT only counts numbers; COUNTA counts every non-empty cell regardless of content type. If a range has 50 numbers and 30 text labels, COUNT returns 50 and COUNTA returns 80. Use COUNT when you specifically need numeric values. Use COUNTA when you want to know how many cells have anything in them.

The most common mistake is reaching for COUNT when you really want COUNTA — for example, counting names in a list. Names are text, so COUNT returns 0. Switch to COUNTA and you get the right answer. The flip side: if your "names" column has stray numbers (like ID codes mixed with names), COUNTA counts them too, which may not be what you want.

COUNT vs COUNTIF: Adding Criteria

COUNT has no criteria argument. To count cells that match a condition (like "values above 1000" or "cells equal to Q3"), use COUNTIF: =COUNTIF(A1:A100, ">1000"). COUNTIF is essentially a filtered COUNT, but it can also count text matches that COUNT ignores entirely.

For multiple criteria, use COUNTIFS: =COUNTIFS(A1:A100, ">1000", B1:B100, "East") counts rows where column A is above 1000 AND column B equals "East". Both functions are case-insensitive for text matching. See our Excel COUNTIF function guide for the deep dive.

COUNT vs COUNTBLANK: The Opposite Job

COUNTBLANK does the inverse of COUNTA — it counts cells that are empty. If a range has 80 filled cells and 20 empty cells, COUNTA returns 80 and COUNTBLANK returns 20. The two together always equal the total cell count of the range.

One gotcha: COUNTBLANK treats cells containing an empty string ("") as blank, but COUNTA does not. That means COUNTA + COUNTBLANK can exceed the total cell count when you have formula-returned empty strings in the mix. Worth knowing if your dashboard math suddenly looks off by a few cells.

COUNT With Errors in the Range

One of COUNT's best features is that it ignores error values. =COUNT(A1:A100) on a range with #N/A or #DIV/0! values returns a clean count of the numeric cells and skips the errors. SUM, AVERAGE, and many other functions return an error when any cell in the range is an error — but COUNT does not.

This makes COUNT a useful "did the formula work?" check. If COUNT returns 95 on a range of 100 lookup formulas, you know 5 are failing. To handle errors in SUM-style aggregations, use the AGGREGATE function or wrap each cell in IFERROR.

Common COUNT Mistakes and How to Fix Them

The most frequent mistake is expecting COUNT to count text. It will not. If your range is all text, COUNT returns 0. Solution: switch to COUNTA or COUNTIF with a wildcard like =COUNTIF(A1:A100, "*") which counts every cell containing any text.

The second mistake is counting numbers stored as text — common when data is imported from CSV or copied from a website. The cell looks like a number but Excel sees text. Fix: use VALUE() or paste-special multiply by 1 to convert. The third mistake is forgetting that dates count as numbers — useful most of the time but surprising when you expected only "real" numeric columns.

Performance and Large Ranges

COUNT is fast on any reasonable range. Even on a million-row column, it returns in well under a second on modern hardware. The function does not need to evaluate cell formulas to count them — it only checks whether the resulting value is numeric. That keeps it efficient even on heavily formula-driven workbooks.

If you find COUNT running slowly, the culprit is usually volatile functions inside the counted range, not COUNT itself. NOW, TODAY, INDIRECT, OFFSET, and RAND all recalculate on any change, dragging down anything downstream — including the COUNT formula reading their output.

Counting in Excel Tables vs Ranges

If your data is in a structured Excel Table, you can reference it by column name: =COUNT(SalesData[Amount]). As the table grows, the formula automatically expands to cover the new rows — no need to rewrite the range. This is the cleanest pattern for any growing dataset.

Tables also make filtering smarter: combine COUNT with SUBTOTAL or use the Total Row at the bottom of the table, which lets you pick COUNT (numbers only) or COUNTA from a dropdown for any column. The Total Row uses SUBTOTAL behind the scenes, so it respects filters automatically.

COUNT With Conditional Formatting

COUNT integrates nicely with conditional formatting for data-quality dashboards. Set up a rule like "Format cells where the formula =COUNT(A1:A100)<COUNTA(A1:A100) returns TRUE" to flag ranges where some cells contain text instead of numbers. Your eyes go straight to the problem rows.

You can also use COUNT inside a formula-based rule to color-code progress: cells above the COUNT-based average get one color, those below get another. This pairs well with the AVERAGE function and gives you a quick visual scan of your data distribution.

COUNT in PivotTables

PivotTables have their own Count function in the Summarize Values By menu — and like the worksheet COUNT, it counts numbers only. Switch to Count Numbers (which is the equivalent of the worksheet COUNT) explicitly if you want this behavior. The default "Count" in PivotTables actually counts all non-empty cells, equivalent to COUNTA.

This naming inconsistency trips people up regularly. If your PivotTable count seems wrong, check which Summarize By option is selected. The right-click Value Field Settings menu gives you the full list including Count, Count Numbers, Average, Max, Min, and so on.

Using COUNT for Quick Data Audits

One of the simplest uses of COUNT is auditing a fresh data import. Open the file, select the column, and look at the status bar at the bottom of the Excel window. By default it shows the count of selected non-empty cells, but you can right-click the status bar and turn on Numerical Count, which shows the COUNT result without typing a formula.

This gives you an instant read on data quality: select a range and look at both Count and Numerical Count side by side. If they match, every non-empty cell is numeric. If they differ, you have a mix of text and numbers — which may be intentional or a sign that import settings need adjustment.

COUNT in Array Formulas and Dynamic Arrays

In Excel 365, COUNT works seamlessly with dynamic arrays. =COUNT(FILTER(A1:A100, B1:B100="East")) counts only the rows where column B is "East" by filtering first, then counting. The result is a single number, since COUNT collapses arrays.

This pattern replaces older array formulas that required Ctrl+Shift+Enter. Combined with FILTER, UNIQUE, SORT, and SEQUENCE, COUNT becomes a building block for compact reporting formulas that used to require helper columns or PivotTables.

Putting It All Together

The decision tree is simple. Count numbers only? Use COUNT. Count anything non-empty? Use COUNTA. Count blanks? COUNTBLANK. Count by criteria? COUNTIF or COUNTIFS. Count visible filtered cells? SUBTOTAL with function number 2 or 3. Count from a structured table? Use the table name and column reference.

Knowing which function to use for each job is half the battle — and once you have it down, your formulas will be shorter, faster, and easier for someone else to audit. For more on related counting and summing functions, see our Excel SUM function guide.

If you are preparing for an Excel certification exam, our masterpage at Excel Practice Test brings together every guide on the site plus 200+ practice questions covering COUNT, COUNTA, COUNTIF, and the rest of the function library. You will hit the test with the right reflexes in place.