Sum Function | Excel Hotshot

The SUM function adds values. You can add individual values in a cell, acell references or ranges or all of them.

 

Syntax:

=SUM(number1,[number2],[number3]...[numberN])

 

Argument Name	Description
number1    Required	The first number you want to add. The number can be like 1, a cell reference like A1, or a cell range like A1:A10.
number2-255    [Optional]	This is the second number you want to add. You can specify up to 255 numbers in this way.

As already explained above, the SUM function adds values. You can add individual values in a cell, acell references or ranges or all of them.

For example:

• =SUM(A1:A10) Adds the values in cells A1:10.

• =SUM(A1:A10, C1:C10) Adds the values in cells A1:10, as well as cells C1:C10.

Best Practices with SUM:

This section will discuss some best practices for working with the SUM function. Much of this can be applied to working with other functions as well.

The =1+2 or =A+B Method – While you can enter =1+2+3 or =A1+B1+C2 and get fully accurate results, these methods are error prone for several reasons:

1. Typos – Imagine trying to enter more and/or much larger values like this:

   • =14598.93+65437.90+78496.23

   Then try to validate that your entries are correct. It’s much easier to put these values in individual cells and use a SUM formula. In addition, you can format the values when they’re in cells, making them much more readable then when they’re in a formula.

   

2. #VALUE! errors from referencing text instead of numbers

   If you use a formula like:

   • =A1+B1+C1 or =A1+A2+A3

   

   Your formula can break if there are any non-numeric (text) values in the referenced cells, which will return a #VALUE! error. SUM will ignore text values and give you the sum of just the numeric values.

   

3. #REF! error from deleting rows or columns

   

   If you delete a row or column, the formula will not update to exclude the deleted row and it will return a #REF! error, where a SUM function will automatically update.

   

4. Formulas won't update references when inserting rows or columns

   

   If you insert a row or column, the formula will not update to include the added row, where a SUM function will automatically update (as long as you’re not outside of the range referenced in the formula). This is especially important if you expect your formula to update and it doesn’t, as it will leave you with incomplete results that you might not catch.

   

5. SUM with individual Cell References vs. Ranges

   Using a formula like:

   • =SUM(A1,A2,A3,B1,B2,B3)

   Is equally error prone when inserting or deleting rows within the referenced range for the same reasons. It’s much better to use individual ranges, like:

   • =SUM(A1:A3,B1:B3)

   Which will update when adding or deleting rows.