How to Count in Excel: COUNT, COUNTA, and More

To count numeric values in a range, enter =COUNT(A2:A10) in an empty cell, assuming the values you want to check are in A2:A10. To count all nonempty cells instead, use =COUNTA(A2:A10). Excel has several count functions, so the right one depends on whether you mean numbers, filled-in cells, blank cells, or entries that meet a condition.

What You’ll Need

A workbook in Microsoft Excel for Microsoft 365 on Windows.
A data range to count, such as a column of names, dates, quantities, or order statuses. Include the data cells, not the heading, unless you intentionally want to count the heading too.
An empty cell where you can enter the result. You can also place a count formula beside or below the data.

The formulas below also work in current Excel for Mac and Excel for the web. The formulas themselves are the same, although regional settings may use semicolons rather than commas between formula arguments.

Step-by-Step

Decide what you want to count. First identify whether your question is “How many numbers are here?”, “How many cells have something in them?”, or “How many entries match a rule?” These are different questions in Excel. For example, a list may contain 12 filled cells but only 9 numeric values because three cells contain text labels. Choosing the question first prevents a correct formula from giving an answer that is correct for the wrong purpose.
Check the range and choose an output cell. Click an empty cell where the answer should appear, then identify the first and last cells containing the data. A range such as A2:A10 includes nine cells in column A. If row 1 contains a label such as “Quantity,” starting at A1 can affect functions that count nonempty cells. Keep the result cell outside the range when possible so it does not accidentally count itself.
Count numeric values with COUNT. With numbers or dates in A2:A10, enter =COUNT(A2:A10). If A2:A10 contains 14, 8, a blank, the text “pending,” 3, 0, a date, 5, and 11, the result is 7: Excel counts the five ordinary numbers, zero, and the date. Excel stores dates as numbers, so COUNT includes them. It does not count text or empty cells in a referenced range.
Count all filled cells with COUNTA. With entries of any kind in A2:A10, enter =COUNTA(A2:A10). For the sample range above, the result is 8 because every cell except the blank contains something. COUNTA includes text, numbers, dates, and errors. It also counts a cell containing a formula even if that formula displays an empty string, which can make its result seem higher than the number of visibly filled cells.
Count blank cells with COUNTBLANK. With the cells to check in A2:A10, enter =COUNTBLANK(A2:A10). The sample range above returns 1. COUNTBLANK is useful for finding missing responses or unfinished fields. It counts truly empty cells and cells whose formulas return an empty string, so it may report a blank even when a formula is present in the cell.
Count entries that meet one condition with COUNTIF. With product names in A2:A10, enter =COUNTIF(A2:A10,"Apples") to count cells whose value is Apples. COUNTIF ignores letter case, so it treats “apples” and “Apples” as matches. For a numeric condition, with sales amounts in B2:B10, enter =COUNTIF(B2:B10,">100"). If the values are 80, 120, 135, 100, 0, blank, 200, 95, and 110, the result is 4 because four amounts are greater than 100. The criteria are in quotation marks, and the comparison is strictly greater than, not greater than or equal to.
Count entries that meet multiple conditions with COUNTIFS. Suppose region names are in A2:A7 and sales amounts for the same six rows are in B2:B7. If the rows are East/120, West/200, East/90, East/150, West/75, and East/100, enter =COUNTIFS(A2:A7,"East",B2:B7,">=100") to count East rows with sales of at least 100. The result is 3. Each condition range must cover the same number of rows and align with the other range, so a condition in A2:A7 is checked against the value on the same row in B2:B7.
Use a cell as the condition when you want an adjustable count. Put the threshold in D1, and with sales amounts in B2:B10 enter =COUNTIF(B2:B10,">"&D1). If D1 contains 100, Excel counts values greater than 100; change D1 to update the result without editing the formula. The ampersand joins the comparison operator to the value in D1. For an exact text match controlled by a cell, use that cell as the COUNTIF criteria argument instead of typing the text into the formula.
Count visible values after filtering, if needed. If IDs are in A2:A20 and you have filtered the list, enter =SUBTOTAL(103,A2:A20) to count visible nonempty cells. SUBTOTAL skips rows removed by a filter; function number 103 also ignores rows hidden manually. If you only want to count visible numeric values, use function number 102 in the same setup. Place the formula outside the filtered list so it remains easy to see.
Confirm the result and save the workbook. Compare the count with a small sample of the source data, especially after applying criteria or filters. Select the formula cell and check the formula bar if the displayed answer looks wrong. Press Enter to commit a formula, and save the workbook after you have confirmed the result.

Common Mistakes

Using COUNT when you mean “filled in.” COUNT ignores text, so it is unsuitable for counting names, labels, or status words. Use COUNTA for nonempty entries, or COUNTIF when you need a particular value.

Including the heading in the range. A heading such as “Name” is text. COUNT generally ignores it, but COUNTA counts it, increasing the result by one. Start at the first data row, commonly row 2, rather than including a heading in row 1.

Assuming a number stored as text is numeric. Values imported from another system may look like numbers but be stored as text. COUNT will not include them. Check the cells for an import warning or inconsistent alignment, and convert the values to numbers if they are intended to be numeric.

Using the wrong comparison sign or criteria format. “Greater than 100” and “at least 100” are different conditions. The first excludes 100; the second includes it. COUNTIF criteria containing an operator need quotation marks, and a cell-based threshold needs the operator joined to the cell value.

Using mismatched ranges in COUNTIFS. If one range starts on row 2 and another starts on row 3, Excel may compare unrelated records or return an error. Keep every criteria range the same size and aligned to the same records.

Tips

Keep a data column consistent. A column intended for quantities should contain quantities, not explanatory phrases such as “not received.” Put explanations in a separate column; this makes counts easier to interpret and reduces surprises caused by mixed data types.

For a quick check without adding a formula, select the cells. Excel’s status bar at the bottom of the window can show a count for the selection; right-click the status bar to choose which summaries appear. The status-bar count can differ from COUNT because it may count nonempty selected cells rather than numeric values, so use a formula when you need a repeatable, clearly defined result.

Use descriptive labels beside formulas, such as “Numeric entries” or “Orders over 100,” so someone reviewing the sheet knows what the number represents. If your list grows regularly, consider using an Excel Table: formulas and ranges are easier to maintain when records are added to a structured table.

Wildcards help count text patterns. In a COUNTIF criterion, an asterisk represents any number of characters, and a question mark represents one character. For example, with email addresses in C2:C100, =COUNTIF(C2:C100,"*@example.com") counts addresses ending in @example.com. If you need to match a literal asterisk or question mark, put a tilde before the wildcard character in the criterion.

Troubleshooting

The formula appears as text instead of calculating. Check whether the cell is formatted as Text or the formula begins with an apostrophe. Change the cell format to General, edit the formula, and press Enter. Also make sure the first character is an equals sign and there is no space or apostrophe before it.

The count is zero even though the cells look numeric. The values may be stored as text. Try selecting a sample cell and checking whether Excel offers a conversion warning. You can use the warning’s Convert to Number option when available, or re-import the source data with the correct data type. Avoid converting values blindly if leading zeros are meaningful, such as in postal codes or product IDs.

COUNTA gives a larger result than the visible entries. Look for formulas that display blank text, spaces, or hidden characters. A space is content, and a formula returning an empty string is still counted by COUNTA. If the cells contain formulas, inspect the formula bar; if they contain imported text, remove unwanted spaces or clean the source data.

A criteria formula misses values that seem to match. Check for extra spaces, different punctuation, or numbers stored as text. COUNTIF text matching ignores capitalization, but it does not automatically remove spaces or make different spellings equivalent. For numeric comparisons, verify that the source values are genuine numbers and that the condition uses the intended boundary, such as greater than versus greater than or equal to.

The answer does not update after a change. Check Formulas > Calculation Options and confirm that Automatic is selected. If calculation is set to Manual, use F9 on Windows to recalculate. On Mac, use Fn+F9 on keyboards where the function keys control system features; the exact key behavior can vary by keyboard settings. If the formula still does not update, confirm that the edited cells are actually inside its range.

Excel rejects commas in the formula. Some regional settings use semicolons as argument separators. If Excel displays a formula error as soon as you enter a formula with commas, replace the separators with semicolons as directed by your Excel settings. The function names and counting logic remain the same.

Frequently Asked Questions

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

How do I count a specific word in a column?
Use COUNTIF with the column range and the word as its criterion. For instance, with statuses in D2:D50, enter =COUNTIF(D2:D50,"Complete") to count cells matching Complete. The formula does not distinguish upper- and lowercase letters.

Can I count cells that contain part of a word?
Yes. Use an asterisk wildcard before and/or after the text in a COUNTIF criterion. For example, with descriptions in A2:A50, =COUNTIF(A2:A50,"*late*") counts cells containing the text late anywhere in the cell. This is a pattern match, so it can also match longer words that include those letters.

How do I count rows that meet two conditions?
Use COUNTIFS, providing a range and condition for each rule. Make sure the ranges have the same dimensions and line up row by row. For example, you can count records for one region where the related amount is above a threshold.

Does COUNT include dates?
Yes. Excel stores dates and times as serial numbers, so COUNT includes valid dates and times in a range. A date-looking entry stored as text is not counted as a numeric date until it is converted to a real Excel date value.

Can Excel count only the rows I can see after filtering?
Yes. SUBTOTAL can ignore filtered-out rows. Use function number 103 to count visible nonempty cells, or 102 for visible numeric values. Keep in mind that the result describes the currently visible records, so changing or clearing the filter can change the count.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.