Using a hash in a sum formula

[lfk73]

I've inherited a spreadsheet for work and noticing some weird use of formulas.  One of them I'm hoping someone can explain.  I have a column like below.  The last cell basically is a sum of all the cells above, so you'd expect something like =SUM(B29:B49) and you get the answer of 1.

However they use =SUM(B29#).  

Plus, assuming it's just another way to sum up the column I put a 1 in another cell and expect an answer of 2.  But that's not what happens.  Instead, I get an error #SPILL!

I though oh maybe the hash means start at B29 until you reach something, but I don't understand what the use of a hash does.

0
0
0
0
1
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
1

[#2]

@lfk73 With MS365, the hash symbol is used to refer to a "spill range", which is the result of a dynamic array formula. Cell B29 in this example has a formula that returns multiple results (array), which extends to cell B49. Only the first cell in the array contains the formula, and only the first cell can be edited. Attempting to change the value of any other cell within the spill range will result in the #SPILL! error.

For more information, please see: https://www.xelplus.com/excel-hash-sign-formulas/ 

[#3]

@lfk73 

I've analyzed the information you've provided and the image, and here's my understanding of the formula =SUM(B29#) and its behavior:

Formula Breakdown:

• SUM(B29#):
  • SUM: This function calculates the sum of a range of cells.
  • B29: This specifies the starting cell of the range.
  • #: This is the spill operator, introduced in Excel 365.

Spill Operator Behavior:

• The spill operator (#) instructs Excel to dynamically expand the formula to include adjacent cells that meet certain criteria.
• In this case, the criteria seem to be empty cells.
• Starting at B29, the formula will sum the values in adjacent cells until it encounters a non-empty cell, effectively summing all values down the column until the first non-empty cell is reached.

Unexpected Behavior:

• You mentioned entering a 1 in another cell and expecting a sum of 2, but getting an error (#SPILL!).
• This is likely because the spill operator is expecting a range to sum, and entering a single value in a cell doesn't create a range.
• To add 1 to the sum, you would need to adjust the formula to include the additional cell, like =SUM(B29#:B30) + 1.

Alternative Approach:

• If you only want to sum the values in cells B29 to B49, you can use the original formula =SUM(B29:B49). This will work as expected without the spill operator.

Summary:

• The formula =SUM(B29#) uses the spill operator to dynamically sum values down the column until the first non-empty cell is encountered.
• It's not a straightforward replacement for SUM with a defined range.
• Consider using =SUM(B29:B49) if you only want to sum specific cells.
• Adjust the formula or use alternative approaches based on your desired outcome.

I hope this explanation clarifies the formula's behavior and helps you choose the best approach for your spreadsheet.