Excel COUNT and SUM Functions: A Practical Guide

Excel has several ways to count and add data, and the difficult part is usually choosing the right function. COUNT and SUM work without conditions. Use COUNTIF or SUMIF for one condition, and the plural COUNTIFS or SUMIFS when every row must satisfy several conditions.

This guide uses one small sales table to make those differences visible. You will learn what each function counts or sums, how to write criteria, and why a formula can return zero even when the worksheet appears to contain matching data.

The formulas below use commas between arguments. Depending on your regional settings, Excel may use semicolons instead. Formula AutoComplete shows the separator and argument order expected by your installation.

Choose the function from the question

The singular functions—COUNTIF and SUMIF—accept one condition. The plural functions—COUNTIFS and SUMIFS—accept one or more range-and-criteria pairs and require all of those tests to be true for a row.

Use one sample table for every example

Assume cells A1:D7 contain this data:

Each data row represents one order. Columns A through C describe it, while column D contains the amount to total.

Count numbers, filled cells, or blanks

This example compares COUNT, COUNTA, and COUNTBLANK on the same Sales range. COUNT returns 6 numeric cells, COUNTA returns 6 non-empty cells, and COUNTBLANK returns 0 blank cells.

COUNT counts numeric values

COUNT counts cells that contain numbers. In the sample, all six sales cells are numeric:

=COUNT(D2:D7)

The result is 6. Dates and times are also counted when Excel recognizes them as numeric date or time values. Text, logical values, errors, and genuinely empty cells in a referenced range are not counted.

This distinction explains a common surprise: a cell that displays 120 may be stored as text. It looks numeric, but COUNT ignores it. Microsoft’s COUNT reference details how direct arguments and referenced cells are evaluated.

COUNTA counts cells that are not empty

Use COUNTA when text, numbers, dates, logical values, and errors should all count as entries:

=COUNTA(C2:C7)

The result is 6 because every Status cell contains a value. A space also makes a cell nonempty, even though it may look blank.

COUNTBLANK counts empty cells

Use COUNTBLANK to count blank cells in one range:

=COUNTBLANK(C2:C7)

The result is 0 for the sample data. One subtle rule matters when formulas produce empty text: a formula that returns "" is counted by COUNTA because the cell contains a formula result, and it is also counted by COUNTBLANK as a blank. The two functions are therefore not always mathematical opposites. Microsoft documents these behaviors in its COUNTA and COUNTBLANK references.

Count records that meet conditions

COUNTIF applies one condition

The syntax is COUNTIF(range, criteria). To count North orders:

=COUNTIF(A2:A7,"North")

This COUNTIF example counts how many rows in the Region column contain North. The formula =COUNTIF(A2:A7,"North") returns 3.

The result is 3. The function checks each cell in A2:A7 and increases the count when that cell equals North.

Criteria can be exact text, a number, a cell reference, a comparison, or a text pattern. For example, this formula counts sales of at least 180:

=COUNTIF(D2:D7,">=180")

The result is 3. The comparison operator belongs inside quotation marks. If the threshold is stored in G2, join the operator to that reference:

=COUNTIF(D2:D7,">="&G2)

The official COUNTIF guide also notes that text matching is not case-sensitive.

COUNTIFS applies several conditions to the same row

Use COUNTIFS when one record must pass several tests. This formula counts rows where Region is North and Status is Closed:

=COUNTIFS(A2:A7,"North",C2:C7,"Closed")

This COUNTIFS example counts rows that meet two conditions at the same time: Region equals North and Status equals Closed. The formula returns 2.

The result is 2. Excel evaluates row 2 across both criteria ranges, then row 3, and so on. A row contributes to the count only when every supplied criterion is true.

All criteria ranges must contain the same number of rows and columns. They do not need to be adjacent, but A2:A7 cannot be paired with C2:C20. The COUNTIFS documentation confirms this alignment requirement.

Add every value or only matching values

SUM adds numbers in one or more ranges

To total all six sales values:

=SUM(D2:D7)

This SUM example adds all values in the Sales column. The formula =SUM(D2:D7) returns a total of 1,010.

The result is 1,010. You can include several ranges or individual numbers as separate arguments, such as =SUM(D2:D7,F2:F7).

SUM ignores text in referenced cells. That makes it safer than a chain such as =D2+D3+D4 when a range may contain labels, but it can also hide a data-quality problem when numbers have been imported as text. Microsoft’s SUM reference covers accepted arguments and common errors.

SUMIF totals values for one condition

The full syntax is SUMIF(range, criteria, [sum_range]). The first range is tested; the optional final range contains the numbers to add. To total sales for North:

=SUMIF(A2:A7,"North",D2:D7)

This SUMIF example adds sales only for rows where Region equals North. The formula returns 510.

The result is 510. Excel finds North in column A and adds the corresponding values from column D.

If the cells being tested are also the cells being added, omit sum_range. This formula adds the sales values that are at least 180:

=SUMIF(D2:D7,">=180")

The result is 640. The SUMIF documentation explains both the two-argument and three-argument forms.

SUMIFS totals values for several conditions

SUMIFS starts with the values to add, followed by range-and-criteria pairs:

=SUMIFS(D2:D7,A2:A7,"North",C2:C7,"Closed")

This SUMIFS example adds Sales values only for rows where Region equals North and Status equals Closed. The formula returns 390.

The result is 390, the combined sales for the two North rows marked Closed.

Do not copy the SUMIF argument order into SUMIFS. In SUMIF, sum_range is the optional third argument. In SUMIFS, sum_range comes first. Microsoft identifies this reversal as a common source of errors in its SUMIFS reference.

Write criteria Excel can interpret

An asterisk matches any sequence of characters, while a question mark matches one character. Prefix either symbol with a tilde when it should be treated literally. Wildcards apply to text criteria in the conditional functions.

Build a conditional formula without guessing

1. Decide whether the output should be a count or a total.
2. Decide whether there are zero, one, or several conditions.
3. Identify the criteria column for each condition.
4. For a sum, identify the separate numeric column to add.
5. Make every criteria range and sum range cover the same data rows.
6. Enter the formula, then test it against a small set of rows you can verify manually.

This process prevents the most common structural mistake: testing one set of rows while summing or counting a different set.

Fix common COUNT and SUM problems

Formatting does not determine the stored data type. A numeric-looking text value remains text after you apply a Number or Currency format; convert the underlying value when it should participate in numeric counts and totals.

Know when another tool fits better

• Use SUBTOTAL or an Excel table Total Row when filters should affect the result.
• Use a PivotTable when you need many grouped counts and totals by region, product, date, or another category.
• Use AVERAGEIF or AVERAGEIFS when the required result is a conditional average rather than a count or sum.
• Clean inconsistent source values before adding more formula complexity. Extra spaces and numbers stored as text can make a correct formula appear wrong.

Microsoft’s broader counting-methods guide compares formula, status-bar, subtotal, database, and PivotTable approaches.

Frequently asked questions

Start with the result you need

Choose count or total first, then choose zero, one, or several conditions. That leads directly to COUNT, COUNTIF, COUNTIFS, SUM, SUMIF, or SUMIFS. When results look wrong, inspect data types, criteria syntax, and range alignment before rewriting the formula.