COUNT and COUNTIF in Excel: Formulas, Examples, Tips

Formulas & functions · Updated November 22, 2026

In Excel, COUNT counts cells containing numbers, while COUNTIF counts cells that meet one condition. With numbers in A2:A100, use =COUNT(A2:A100); with order statuses in B2:B100, use =COUNTIF(B2:B100,"Paid") to count cells whose value is Paid, regardless of capitalization.

What COUNT and COUNTIF do

Both functions return a count, but they answer different questions. COUNT asks, “How many cells in this range contain numeric values?” COUNTIF asks, “How many cells match this condition?” Choosing the right function depends on what the cells contain and what you want to measure.

COUNT is useful for numerical records such as quantities, scores, dates, times, or amounts. Excel stores dates and times as numbers, so COUNT includes them. It also includes zero: zero is a number, not a blank. COUNT does not count ordinary text, empty cells, or text that merely looks like a number when that text is stored in a cell.

COUNTIF can count numbers or text according to a criterion. It can find an exact status, count values above a threshold, or match text that contains a particular word. It is often used for quick summaries—for example, the number of orders marked Paid or the number of test scores at least 70.

How COUNT works

COUNT accepts one or more references or values and returns how many of the referenced cells contain numbers. With numeric entries in A2:A8, including a zero, a date, a text entry that looks numeric, and one blank, =COUNT(A2:A8) returns 3 if only three cells contain actual numeric values. The date counts as a number; the zero counts; the text-looking number and blank do not.

A formula in a cell is counted when its result is numeric. For example, a cell containing a calculation that returns 12 counts, while a formula that returns a text label does not. This is why a column can look full but produce a smaller COUNT result: some entries may be text, spaces, or formulas returning text rather than numbers.

To count numeric values across separate ranges, COUNT can accept multiple arguments. With scores in A2:A20 and additional scores in C2:C20, =COUNT(A2:A20,C2:C20) counts numeric cells in both ranges. This is useful when a worksheet has separate blocks of data, though keeping related values in one column usually makes later analysis easier.

How COUNTIF criteria work

COUNTIF uses two arguments: the range to inspect and the criterion to apply. The criterion may be a number, text, a comparison expression, a wildcard pattern, or a reference to a cell containing the desired condition. The range is the same set of cells being evaluated; COUNTIF does not return or add the matching values.

For an exact text match, with statuses in B2:B8, =COUNTIF(B2:B8,"Paid") counts cells containing Paid. The comparison is not case-sensitive, so Paid, PAID, and paid all match. Extra spaces are different, however: a cell containing Paid followed by a trailing space may not match the criterion exactly.

To count numbers above a threshold, with scores in C2:C50, =COUNTIF(C2:C50,">=70") counts scores of 70 or higher. The comparison operator is part of the criterion text. Excel supports operators such as greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), not equal to (<>), and equal to (=).

If the threshold is stored in a cell, join the operator to that cell reference. With scores in C2:C50 and the threshold in E2, =COUNTIF(C2:C50,">="&E2) counts scores at least as large as E2. The ampersand combines the operator text with the cell value; typing >=E2 inside quotation marks would instead make Excel look for that literal text.

Useful examples for text, dates, and partial matches

COUNTIF supports wildcards in text criteria. An asterisk (*) represents any number of characters, including none, and a question mark (?) represents exactly one character. With product names in A2:A100, =COUNTIF(A2:A100,"North*") counts names that begin with North, such as North Ridge or North. With codes in A2:A100, =COUNTIF(A2:A100,"AB??") counts entries that start with AB and have exactly two more characters.

To search for a literal asterisk or question mark rather than use it as a wildcard, precede it with a tilde (~). With labels in A2:A100, =COUNTIF(A2:A100,"~*") counts cells whose value is a literal asterisk. This is a less common need, but it can matter when counting product codes or imported data that use wildcard characters as real text.

Dates can be counted using the same comparison rules because Excel stores dates as serial numbers. For a date range in A2:A100 and a start date in E2, =COUNTIF(A2:A100,">="&E2) counts dates on or after the date in E2. To count dates within a period, use COUNTIFS, which applies multiple conditions. With dates in A2:A100, a start date in E2, and an end date in F2, =COUNTIFS(A2:A100,">="&E2,A2:A100,"<="&F2) counts dates inclusively between those endpoints. Confirm that the source entries are real Excel dates, not text that only looks like dates.

Choosing between COUNT, COUNTIF, and related functions

Use the function that matches the question rather than trying to make one function cover every kind of count. These are the usual choices:

  • COUNT counts numeric cells, including dates, times, and zero values.
  • COUNTA counts cells that are not empty, including cells containing text. A formula that displays an empty string can make “nonempty” counts behave differently from what a reader expects, because the cell still contains a formula.
  • COUNTBLANK counts blank cells. It is useful for checking whether required fields have been left empty.
  • COUNTIF applies one condition to a range.
  • COUNTIFS applies two or more conditions, such as counting orders that are Paid and dated within a selected period.

For example, with order statuses in B2:B100 and regions in C2:C100, =COUNTIFS(B2:B100,"Paid",C2:C100,"West") counts rows where the status is Paid and the region is West. Each condition has its own range and criterion. The ranges should be the same size and aligned row by row so Excel evaluates the intended records together.

A common misconception is that COUNTIF adds the values that match. It does not: it returns how many matches there are. To add values conditionally, use SUMIF or SUMIFS instead. For instance, a count of paid orders answers “how many,” while a conditional sum of their amounts answers “how much.”

Common counting problems and how to fix them

If COUNT gives a lower result than expected, check whether the apparent numbers are stored as text. Imported values, leading apostrophes, or inconsistent data entry can cause this. Select a suspicious cell and inspect its contents or number format, but do not rely on appearance alone: a numeric-looking value can still be text. Converting the source data to numbers may be necessary before COUNT can include it.

If COUNTIF returns zero for a text criterion that looks correct, inspect the source for leading or trailing spaces, nonbreaking spaces, or spelling differences. COUNTIF ignores letter case but does not generally ignore extra characters. Cleaning the data or using a carefully chosen wildcard criterion can resolve the mismatch; wildcards should not be used casually if they could match unintended entries.

Blank criteria also deserve care. With labels in A2:A20, =COUNTIF(A2:A20,"") counts cells Excel treats as blank. A cell containing a formula that returns an empty string is not the same thing as a truly empty cell, so test the formula against your data if those cells are present. Likewise, a criterion of "<>" is commonly used to count nonblank entries, but formulas returning empty strings can affect the result because those cells still contain formulas.

Another source of confusion is regional settings. The formulas shown here use commas between arguments, as in standard English-language Excel syntax. Some installations configured for other regional conventions use semicolons as argument separators. If Excel rejects a correctly written formula, check the list separator used by your settings rather than changing the function name.

Entering and checking the formulas

In Excel for Microsoft 365 on Windows, select a cell for the result, type an equals sign and the function, then select the range or type its address. For example, with the values to evaluate in D2:D40, =COUNT(D2:D40) returns the number of numeric cells in that range. Press Enter to calculate. To inspect the logic, select the result cell and look at the formula bar.

COUNT and COUNTIF are longstanding worksheet functions and are available in current desktop Excel for Windows and Mac, as well as Excel for the web. The basic formula syntax is the same across these platforms. Some keyboard shortcuts and ribbon details differ between Windows and Mac, but you can enter the formulas directly in a cell on either system. When copying a formula, check that the referenced ranges still cover the intended data; relative references shift when copied.

For a quick check without building a formula, selecting a range of numeric cells may show a count on the Excel status bar, depending on which status-bar calculations are enabled. That display is convenient for a one-time inspection, but a worksheet formula is better when the count must remain visible, update as data changes, or feed another calculation.

Frequently Asked Questions

Does COUNT include zero in Excel?
Yes. Zero is a numeric value, so COUNT includes a cell containing 0. It does not count a blank cell as zero.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF text matching is case-insensitive. Paid and PAID match the same text criterion. If capitalization must matter, COUNTIF alone is not the right tool.

How do I count cells that contain a word anywhere?
Use asterisks around the word as wildcards. With descriptions in A2:A100, =COUNTIF(A2:A100,"*urgent*") counts cells containing urgent anywhere in the text, including longer phrases.

Can COUNTIF count cells greater than a value in another cell?
Yes. Join the comparison operator to the reference with an ampersand. For scores in B2:B50 and a cutoff in E2, =COUNTIF(B2:B50,">"&E2) counts values greater than the cutoff.

Why does COUNT ignore a number that I can see?
The value may be stored as text rather than as a number, or it may contain an invisible character. Check the source data and convert text-formatted numeric entries when appropriate; formatting a cell to display as a number does not by itself always convert its underlying value.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.