SUM function in Excel – the different ways to generate it

SUM function in Excel – the different ways to generate it, have you tried them all?

This week’s hint and tip is about the SUM function in Excel and the different ways that you can go about creating it. This function is​ covered on the Basic Excel training course and we are going to go through the different ways that you can create it on your computer.

The most common function in Excel is clearly the SUM function  and although most people generate this through AutoSum there are several other adaptations and ways of getting this.

AutoSum

This is found on the Home Ribbon in Excel. There is also more than one option you can use it for if you click on the drop down arrow. The drop down arrow next to it allows you to so the SUM function but also some other common simple functions e.g. COUNT and MAX. To use this option you can either select your numbers and click on the AutoSum button and it will work it out for you, or you can click into the empty cell at the bottom of the list and click the AutoSum button and hit Enter and it will work it out again for you.

QA button

To use this option, firstly select the list of numbers and once you have done this the QA button should appear bottom left of the list. Click on this button and select the Totals heading. Under here click on the Sum option and it will have worked out the answer for you.

Typing in the SUM function

Another way is to just type in the SUM function into the cell. To do this, select the empty cell at the bottom of your list of figures and type in =SUM( from here you then need to select the range of cells you want adding up and then type in a close bracket ). Altogether the formula should look something like this: =SUM(A1:A3) (with the references being the numbers you selected). Then hit enter and it will have worked out the answer for you.

Alt =

This is a shortcut way of doing the SUM function. You can either select the cells and type in the shortcut or you can just select the empty cell at the bottom of the list of numbers and type in the shortcut. Both will work out the answer for you. For multiple SUM formulas select the empty cells into which they will go and then type in the shortcut. This will then work out the SUM formula for multiple SUM formulas.

Creating a ‘nested function’ using INDIRECT within the SUM function

Nesting the INDIRECT function within your SUM function allows you to ensure that your SUM function will always include the first number at the top of your list in row 1 even when a new row is inserted above it. An example of this would be this function: =SUM(INDIRECT(“b1”):B4). This example is ensuring that the cell b1 is always included no matter how many rows are inserted above it.

The video below shows you how to create the SUM function in the different ways on your computer in Excel.

Take a look below at the video to find out more and then try it out on your own computer! These are all summarised in text above too.