COUNT Formula in Excel for Text and Numbers Explained

Formulas & functions · Updated November 22, 2026

To count numeric values in cells A2:A100, use =COUNT(A2:A100); COUNT does not count text. To count text, use =COUNTIF(A2:A100,"*"), and to count all nonempty cells use =COUNTA(A2:A100). Choose the formula according to what you mean by “count”: numbers, text, or every populated cell are different totals.

What each Excel count formula includes

Excel has several counting functions because a cell can contain a number, text, a date, a formula, an error, or nothing. The function name alone does not tell you which of those the result will include. These distinctions matter when you are checking an imported list, summarizing survey answers, or reconciling a worksheet against a source.

For a simple range, the main choices are:

  • COUNT counts numeric values.
  • COUNTIF counts cells that meet one condition, such as containing text or matching a label.
  • COUNTA counts cells that are not empty.
  • COUNTBLANK counts blank cells.
  • COUNTIFS counts cells that meet multiple conditions.

These functions count cells, not characters or words. For example, a cell containing the sentence “Order delayed” is one text cell, regardless of how many words it contains. A count formula also does not normally change the data; it returns a total in the cell where you enter it.

Count numbers with COUNT

With values in A2:A20, enter =COUNT(A2:A20) to count numeric entries in that range. If the range contains 14 numbers, three text labels, and three empty cells, the result is 14.

COUNT treats dates and times as numbers because Excel stores them as numeric serial values and displays them using date or time formatting. It also counts formulas whose results are numbers. It ignores ordinary text, blank cells, and logical values such as TRUE and FALSE when they are in referenced cells. An error value is not counted as a number by COUNT.

A frequent surprise is that a value that looks numeric may actually be text. For example, an imported value such as “00427” may be stored as text to preserve its leading zeros. COUNT skips it, even though it looks like a number on the screen. To check a suspicious cell, select it and look at the formula bar and number format, or test it in a temporary cell. Changing the format to Number alone does not always convert text into a real number.

Count text with COUNTIF or ISTEXT

With text entries in A2:A20, use =COUNTIF(A2:A20,"*") to count cells containing text. The asterisk is a wildcard that matches a sequence of text characters; it does not match ordinary numeric values. As a result, the formula counts text labels and numbers stored as text, but not genuine numeric values.

For an explicit test of the value type, with the same range use =SUMPRODUCT(--ISTEXT(A2:A20)). ISTEXT checks each cell and returns TRUE for a text value; the double unary operator turns those TRUE/FALSE results into 1s and 0s, and SUMPRODUCT adds them. This can be easier to reason about when the range contains formulas or imported data, because the formula is directly asking whether each result is text.

There is an important distinction between text that looks like a number and a number stored as a number. If A2 contains the text “52,” both text-counting approaches treat it as text; COUNT does not include it. If A2 contains the numeric value 52 with a custom display format that adds quotation marks or leading zeros, it remains numeric and COUNT includes it. The underlying value, not just its appearance, determines the result.

Count text and numbers together

With entries in A2:A20, use =COUNTA(A2:A20) when the question is “How many cells are populated?” COUNTA includes text, numbers, dates, logical values, and errors. It is often the right formula for counting filled-in responses when every kind of response should qualify.

COUNTA has a caveat: a formula that displays an empty string is still a formula in the cell, so COUNTA counts that cell. This can make its result seem larger than the number of visibly filled cells. If you specifically need the number of numeric values plus text strings that contain at least one character, with entries in A2:A20 use =COUNT(A2:A20)+COUNTIF(A2:A20,"?*"). The question mark requires at least one text character, so this approach excludes a formula result that is an empty string. It also excludes logical values and errors, unlike COUNTA. Choose based on the definition of “filled” that fits your task.

Do not add COUNT and COUNTA to get a total. COUNTA already includes numeric cells, so that would count numbers twice. Use COUNTA alone for all nonempty cells, or combine separate functions only when you deliberately want distinct categories.

Count cells that match a condition

COUNTIF is useful when “text” means a particular word or label rather than any text. With status values in A2:A20, use =COUNTIF(A2:A20,"Approved") to count cells whose contents match Approved. Text criteria are not case-sensitive in COUNTIF, so Approved and APPROVED are treated as the same match.

You can use wildcards for partial text matches. With descriptions in A2:A20, enter =COUNTIF(A2:A20,"*late*") to count cells containing the letters late anywhere in the text, such as “late delivery.” An asterisk matches any number of characters; a question mark matches one character. To search for an actual asterisk or question mark, put a tilde before it in the criterion.

For multiple conditions, use COUNTIFS. With regions in B2:B100 and sales amounts in C2:C100, enter =COUNTIFS(B2:B100,"West",C2:C100,">=100") to count rows where the region is West and the amount is at least 100. The ranges must have matching dimensions. COUNTIFS is useful for filtered-style questions, such as counting records for one department within a date range, without manually sorting the data.

Count blanks and understand formula-generated blanks

With cells in A2:A20, use =COUNTBLANK(A2:A20) to count blank cells. COUNTBLANK includes cells that are truly empty and cells whose formulas return an empty string, such as a formula displaying no visible result. That behavior differs from COUNTA, which counts a formula cell even when its displayed result is empty.

It helps to distinguish three cases: a cell that has never had a value, a cell containing a formula that returns an empty string, and a cell containing a space character. The first is empty. The second looks empty but contains a formula. The third contains text, even if the space is hard to see. These differences explain why COUNTBLANK, COUNTA, and a visual inspection can appear to disagree.

If a total seems wrong, select one of the cells in question and inspect the formula bar. You can also use a temporary test formula such as ISTEXT or ISNUMBER on an individual cell. Remove the test afterward if you do not need it. Be cautious about deleting spaces or converting values in the original data: those changes can affect other formulas and may remove intentional formatting or identifiers.

Choose the right formula for your worksheet

Before entering a count formula, decide exactly which entries should qualify. A count of numeric measurements, a count of nonblank survey responses, and a count of text labels are different questions, even if they use the same range.

  1. Identify the range that contains the records, excluding headings if they should not be counted.
  2. Decide how to treat dates, text-formatted numbers, errors, and formulas that appear blank.
  3. Use COUNT for numeric values, COUNTIF or ISTEXT for text, COUNTA for populated cells, or COUNTBLANK for blanks.
  4. Test the result against a small sample by counting a few known entries manually.

For instance, if A1 is a heading and the data runs from A2 through A101, use A2:A101 rather than A:A when you do not want the heading included. A whole-column reference can be convenient as the list grows, but a bounded range makes it easier to see which records the formula is counting. Tables are another option: when a range is formatted as an Excel table, structured references can expand with added rows, though the formula depends on the table and column names.

Common reasons a count looks incorrect

The most common issue is mixed data types: some entries are real numbers while others are numbers stored as text. This often happens after copying from websites, exporting reports, or combining files. COUNT will skip text-formatted numbers, while a text count may include them. If the values should be numeric, convert them intentionally and check that identifiers such as postal codes or account numbers are not being treated as quantities.

Other causes include a heading included in the selected range, hidden spaces in text, formulas returning empty strings, and an incorrect condition in COUNTIF. A criterion such as “Approved” will not match “Approved ” if an extra trailing space is present. Also remember that COUNTIF text matching is case-insensitive, so it cannot distinguish labels solely by capitalization.

When troubleshooting, compare the function to the question it answers. If you want all visible words or labels, COUNT is the wrong tool. If you want only numeric measurements, COUNTA may include unwanted text and errors. Correcting the definition first is usually more useful than trying several formulas at random.

Frequently Asked Questions

Does COUNT count text in Excel?
No. COUNT counts numeric values in a referenced range and ignores ordinary text. Use COUNTIF with a text wildcard or SUMPRODUCT with ISTEXT when you need to count text.

How do I count both text and numbers in a range?
Use COUNTA to count cells that are not empty. Be aware that it includes error values, logical values, and formulas that return an empty string, not just visible text and numbers.

Does COUNT include dates?
Yes. Excel stores dates and times as numbers, so COUNT includes them when they are in the referenced range. Their date or time appearance comes from cell formatting.

Why does COUNT ignore a number that I can see?
The value may be stored as text, often after an import or copy-and-paste. Check the cell’s underlying value and convert it to a genuine number only if that is appropriate for the data.

How do I count only one specific text value?
Use COUNTIF with the range and the value as its criterion. For example, a range of status entries can be counted for the exact label Approved; the match is not case-sensitive.

What is the difference between COUNT and COUNTA?
COUNT counts numeric values, including dates. COUNTA counts cells that contain something, including text, numbers, errors, logical values, and formulas that display an empty string.

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.