Excel SUM Divided by Count: Calculate an Average

To add a range in Excel and divide by the number of numeric cells, use =SUM(B2:B6)/COUNT(B2:B6) when the values are in B2:B6. The result is their arithmetic average; Excel’s simpler equivalent for this purpose is =AVERAGE(B2:B6).

What SUM divided by COUNT calculates

SUM adds the numeric values in a range. COUNT returns how many cells in that range contain numbers. Dividing the total by that count gives each included number equal weight: add the values, then split the total evenly across the number of values. That calculation is the arithmetic mean, commonly called the average.

The divisor matters as much as the total. If you divide by the number of rows rather than the number of numeric entries, the result can change when the range contains blanks or text. COUNT is usually the intended choice when you want to average numeric observations and skip empty cells. If your definition of “number” is different—for example, every scheduled day, including days with no recorded value—you may need a different divisor.

The formula updates when the values in its range change. You can enter it in a cell outside the data, such as B7 when the data ends at B6. Excel recalculates the result when the workbook recalculates, so you do not need to add the values or count the entries manually.

A worked example with the result

Suppose B2:B6 contains 12, 15, 18, a blank cell, and 25. The total of the four numeric entries is 70, and COUNT returns 4 because it ignores the blank. With those values in B2:B6, enter:

=SUM(B2:B6)/COUNT(B2:B6)

The result is 17.5: 70 divided by 4. The blank does not become a fifth value or reduce the average. This is often useful for survey scores, completed sales, or other lists where an empty cell means that no numeric observation was entered.

For a direct average of the same data, with the values still in B2:B6, use:

=AVERAGE(B2:B6)

It also returns 17.5. AVERAGE is shorter and communicates the intent clearly; writing SUM divided by COUNT can be useful when you want to see or customize the total and divisor separately.

COUNT, COUNTA, and what gets included

COUNT counts cells containing numbers, including dates and times because Excel stores them as numeric values. In a normal cell range, it ignores empty cells and text. A number stored as text is not counted as a number by COUNT, even if it looks like digits on the worksheet. SUM and COUNT therefore generally work together to average numeric entries.

COUNTA has a different job: it counts non-empty cells, including cells containing text. That can be appropriate when each non-empty record should count, but it can produce a misleading average if the sum includes only numeric values while the divisor also includes labels or notes. For example, if a range contains three numeric scores and one text entry, SUM divided by COUNT uses three as the divisor; replacing COUNT with COUNTA uses four.

With numeric values in B2:B5 and a text entry among those cells, this formula uses the number of non-empty cells as its divisor:

=SUM(B2:B5)/COUNTA(B2:B5)

Use it only if that count matches the rule for your analysis. If a text cell represents a missing score, counting it as an observation will lower the calculated average. If the text is a genuine record that should count as zero, it is clearer to enter an actual numeric zero when appropriate and use COUNT.

Choose the denominator that matches the question

Before selecting a counting function, decide what one observation means in your worksheet. The right calculation depends on the data and the question, not just on which formula is easiest to type.

Average recorded numeric values: Use COUNT as the divisor, or use AVERAGE. Blank cells and text entries are excluded; numeric zeros are included.
Average across a fixed number of periods: If every period must count, even when no value was recorded, dividing by the planned number of periods may better match the question. Decide whether a missing period should be treated as zero or left unreported before calculating.
Count all filled records: COUNTA may fit when every non-empty cell represents an observation, but check that text entries should count along with numbers.
Average only entries that meet a rule: Use a conditional average function, such as AVERAGEIF, rather than including unrelated rows in the total or denominator.

For example, an unrecorded daily temperature is not necessarily the same as a temperature of zero. Treating a blank as zero changes both the total and the intended meaning. The formula can calculate either approach, but the worksheet’s assumptions determine which answer is useful.

How blanks, zeros, and text affect the average

A blank cell is normally omitted from both SUM and COUNT. A zero is a numeric value: it adds nothing to the total, but it increases the count and can lower the average. This distinction is important in budgets, inventory, grades, and measurements. A blank may mean “not entered,” while zero may mean “measured and found to be none.”

Consider B2:B5 containing 10, 0, 20, and a blank. The sum is 30 and COUNT returns 3, so the average is 10. If the zero instead were blank, the sum would remain 30 but COUNT would return 2, making the average 15. Neither result is automatically right; each answers a different question about whether zero is an observed value.

Text in a referenced range is generally ignored by SUM and COUNT. However, a numeric-looking value imported or typed as text may be skipped, which can make the average seem unexpectedly high or low. Check the cell’s value and format, and convert text numbers to numeric values if they are meant to participate in the calculation. Also note that an error value in the referenced range can cause the calculation to return an error rather than an average.

Average only rows that meet a condition

Sometimes the goal is not to average every number, but to average values for a particular category, location, or status. AVERAGEIF applies a condition to one range and averages corresponding numeric values from another range. This avoids manually filtering rows or building a separate sum and count that might not select the same records.

For example, suppose region names are in A2:A6 and corresponding sales amounts are in B2:B6. To average sales for rows marked North, enter:

=AVERAGEIF(A2:A6,"North",B2:B6)

Excel averages the numeric sales values in B2:B6 only where the corresponding cell in A2:A6 matches North. The criteria text is in straight double quotation marks in the formula. If the category is stored in another cell, you can use that cell as the criterion instead, which makes it easier to change the selection. Check that the criteria range and the values range cover corresponding rows.

Common errors and ways to prevent them

If the range contains no numeric values, COUNT returns zero. Dividing by zero produces the Excel error #DIV/0!. This can happen when a range is empty, when its apparent numbers are stored as text, or when the formula points to the wrong cells. Confirm the referenced range and check whether the entries are numeric before changing the formula.

If the calculation should display a blank when there are no numeric entries, and the data is in B2:B6, use:

=IF(COUNT(B2:B6)=0,"",SUM(B2:B6)/COUNT(B2:B6))

This checks the count first and returns an empty text value when the count is zero; otherwise it performs the division. It prevents the no-values case from displaying #DIV/0!, but it does not fix unrelated errors in the input cells. Avoid hiding errors indiscriminately if you need to spot bad source data.

Other common checks include making sure the formula covers every intended row, avoiding an accidental mismatch between ranges, and confirming that no unwanted text or error values are present. If you copied the formula down a worksheet, inspect the references to ensure they moved as intended. A formula that works for one group can still point to the wrong rows in the next group.

When to use AVERAGE instead

For a straightforward mean of a numeric range, AVERAGE is usually the most readable option. It expresses the purpose directly and, like SUM divided by COUNT, ignores blank cells and text while including numeric zero values. It also avoids repeating the same range twice, which can reduce the chance that the total and count accidentally refer to different cells.

Use separate SUM and COUNT calculations when you need to display the total and number of observations independently, explain the arithmetic, or deliberately choose a denominator other than the numeric count. If you do customize the divisor, label the result clearly. A total divided by the number of scheduled days, for example, is not necessarily the same statistic as the average of recorded numeric values.

Frequently Asked Questions

What is the Excel formula for sum divided by count?
For numeric values in B2:B6, use =SUM(B2:B6)/COUNT(B2:B6). It adds the numeric cells and divides by how many numeric cells are in that range.

Is SUM divided by COUNT the same as AVERAGE?
For the same range of ordinary cell values, yes: both calculate the arithmetic mean of numeric entries, ignoring blanks and text while counting zeros.

Does COUNT include blank cells or zero values?
COUNT ignores genuinely blank cells but includes numeric zeros. A zero affects the average by increasing the number of values, even though it does not increase the sum.

Why do I get #DIV/0!?
The divisor is zero, usually because the range has no numeric entries or the apparent numbers are stored as text. Check the range and the cell values, or add a deliberate check for a zero count.

Should I use COUNT or COUNTA?
Use COUNT to divide by the number of numeric observations. Use COUNTA only when every non-empty cell, including text, should count as an observation; otherwise its larger divisor can make the result misleading.

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.