Formula Errors

What the Hash Symbol Means in Excel SUM Formulas and Spill Ranges

Tauseeq MagsiTauseeq Magsi Nov 12, 2026 871 views

Question details

The user wants to understand the meaning of the hash symbol (#) in an Excel SUM formula such as =SUM(B29#).

What Does the Hash Symbol Mean in Excel SUM Formulas?
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user is summing results that may come from a dynamic-array formula beginning in cell B29.
Observed behavior
The # symbol references the complete spilled result returned from the formula anchor cell, and a blocked output area can produce a #SPILL! error.
Before you start

Check whether cell B29 contains a dynamic-array formula and identify the cells currently filled by its results. If you need to total a predefined set of cells instead, determine the exact first and last cell for the fixed range.

Solution 1Recommended

Use the # operator to sum a dynamic spilled range

Use this method when B29 is the anchor cell for a dynamic-array formula and you want the SUM to update automatically as the returned results change.

In =SUM(B29#), the # character is the spilled-range operator. It refers only to the complete dynamic-array output that starts in B29; it does not mean every cell below B29 until a blank cell is reached.

1
Locate the formula anchor cell

Select cell B29 and check the formula bar. B29 should contain the dynamic-array formula that produces multiple results.

2
Enter the spilled-range reference

Select the cell where you want the total, type =SUM(B29#), and press Enter.

3
Avoid editing spilled cells

Edit the formula in the anchor cell B29 rather than an individual cell in the spilled output, because the returned cells are controlled by the anchor formula.

4
Clear any spill obstruction

If Excel displays #SPILL!, select the error cell and review the indicated spill range. Clear or move any value, merged cell, or other obstruction in the required output area.

Use the # operator to sum a dynamic spilled range
The spill range can change: As the dynamic-array formula returns more or fewer results, B29# automatically expands or contracts, and the SUM result updates with it.
WPS Spreadsheets formula tools

Sum Dynamic or Fixed Ranges Easily in WPS Spreadsheets

WPS Spreadsheets lets you build and review SUM formulas in a familiar worksheet interface. Use a fixed reference when you know the exact cells to total, then inspect formulas and source values directly in the formula bar.

  1. 1. Open the worksheet in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the values to total.
  2. 2. Select the destination cell: Click the empty cell where you want the total to appear.
  3. 3. Enter the SUM formula: Type =SUM(B29:B49) in the formula bar to total the fixed range, then press Enter.
  4. 4. Verify the referenced cells: Click the formula cell and review the highlighted range on the worksheet to confirm it includes the intended values.
Create clear SUM formulas for fixed worksheet ranges.Review formula references and cell values from the formula bar.Open and edit Microsoft Excel .xlsx and .xls spreadsheets.Use a lightweight spreadsheet application with familiar worksheet controls.
microsoft office alternative - wps office

Frequently Asked Questions

What does B29# mean in an Excel formula?

B29# means the entire spilled result of the dynamic-array formula whose anchor cell is B29. It references all results currently returned by that formula.

Does B29# include every cell below B29?

No. B29# includes only the cells in the dynamic spill range from the formula in B29. It does not continue downward to the next blank or nonblank cell.

Why does =SUM(B29#) return a #SPILL! error?

A #SPILL! error occurs when Excel cannot place the dynamic-array results in the required output area. Select the error cell, then clear or move the cells or merged ranges blocking the displayed spill area.

Can I edit one cell inside a spilled range?

No. Only the anchor cell, such as B29, contains the formula. To change spilled results, select the anchor cell and edit its formula.

How do I sum cells B29 through B49 without using a spill range?

Enter =SUM(B29:B49). The colon creates a fixed range from B29 to B49, so the formula does not depend on a dynamic-array spill result.