SUM Formula – a very useful formula in Excel

This weeks blog is all about that most favourite of functions in Excel the SUM formula! This formula is often seen as one of the favourite functions in Excel as a lot of people need to add up a list of figures for whatever reason and the SUM formula makes it a lot easier!

Generally the formula takes the following format: =SUM(range of cells)

An example of this would be =SUM(b2:b10). This example would add up the contents of 9 cells (cells b2 to b10).

Another example would be =SUM(b2:b10,b14,b20:b25). Here we are mixing the : and , symbols within the brackets.

: this symbol is for a range of figures

, this figure is for discrete figures

Another example can be seen in the SUMIF formula, an example being =SUMIF(b2:b10,”>20″). Here we have added some criteria to the adding operation of SUM so that it only adds those cells in the range b2 to b20 together if the cell contains a number greater than 20.

Our final example is using SUM and INDIRECT together.

=SUM(INDIRECT(“b1”):b4)

Here we fix effectively cell b1 so that if we insert a row above b1 is is still contained within the SUM range. (this is not the case with normal SUM)

This is a more advanced feature and the use of INDIRECT in other ways is contained on our Excel Master class course.

Above we have covered a number of uses for SUM and some examples for it. The basic version of SUM is covered in our Basic Excel course and is often the most popular version of SUM that is used day to day. The other versions are found from our Intermediate up to our Master Class levels of Excel courses.

To find out more about the courses, you take a look at the agendas for our Excel courses on our website here.

Any queries? Please feel free to email us on johnlegge@jplcomputer.co.uk

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We have placed cookies on your device to help make this website better.

You can use this tool to change your cookie settings. Otherwise, we’ll assume you’re OK to continue.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy