written by
Chris Chua

SUM: The Leader of Excel Functions

Microsoft Excel 2 min read

1. SUM is the most common function in Excel

As the first of the 51 Excel functions that will change your life, the SUM function is the only one that has its own keyboard shortcut (Alt + =) and one of the few functions that earns a spot on the HOME ribbon...

AutoSum in Excel HOME ribbon

... and also again on the FORMULAS ribbon.

AutoSum in Excel FORMULAS ribbon

I hope I've impressed upon you that SUM is the most common Excel function since Excel designed SUM to be accessible in so many different places/ways.

Some usage of the SUM function includes:

  • summing of revenue, costs and profits over a 12-month period
  • summing of student scores across different subjects
  • summing of total amount to pay for a number of invoices
  • summing total portfolio value for holdings across different asset classes

2. SUM adds both positive and negative numbers

The SUM function is used to add values together. This includes both positive and negative numbers. So instead of having a formula that adds some numbers and subtract the others, it is easier to use negative numbers and SUM to add up all the numbers.

3. SUM only recognizes numbers

Recall that SUM is used to add values together. It does not make much sense to be adding text together, or text to numbers. Thus, one of the neat tricks of SUM function is that SUM only recognizes numbers and ignores the rest.

To take advantage of the fact that SUM ignores non-numerical values, we can use the SUM function on a range of cells even if some cells contain text. In the example below, it is more convenient to say =SUM(A1:F1) than =A1+A3+A5.

SUM only add numbers
The SUM function only recognizes numbers and ignores non-numerical values.

4. SUM works best with cell ranges

The SUM function works with both cell references:

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

and cell ranges

=SUM(A1:A3,B1:B3)

Which one should we use?

If rows and columns are inserted or deleted within the cell references, the formula can break. However if cell ranges are used, the SUM function will update the formula when adding or deleting rows and columns.

Share your thoughts

Do you have any tips for using the SUM function? Comment in the discussion section below to share your favorite tips with all of us!

Stay informed

Share this article with your friends and colleagues if you think this will benefit them.

Also leave down your email below to receive the latest Excel articles.

Excel functions