I often say to my delegates that there is more than one way to do many things in Microsoft. I have to say this example surprised even me when I considered how to add up numbers in Excel. I came up with this huge list. Some don’t use formulae, most do. Some are more tricky whilst some are very easy. But which one should I use? That depends very much on how your data is arranged and why you need to add totals.
Can you think of any others? Do you have a preference? Are there any you didn’t know?
1. Highlight all the cells to be summed. Look towards the bottom right of the Excel window to see the total
2. Use the SUM formula using the FX function wizard or autosum button on the home tab of the ribbon
3. Add the cells manually using a formula, such as =A3+G6+D6+L3
4. Write the sum formula manually =sum(D2:D99)
5. Use DSUM formula
6. Use the subtotals function from the DATA tab
7. Use SUBTOTAL formula (different to the subtotals button on the DATA tab!)
8. Use consolidate function if you are adding up across different sheets
9. Create a pivot table
10. Filter just the items you want to add up
11. Use a SUMIF formula where you are only adding up specific items
12. Use a SUMIFS if there are multiple criteria
13. Format the data as a table and then add the totals row to the bottom
Do you know any others?
......And as a little nod to Microsoft Word, did you know you can add up numbers in a table in Word? – add the numbers to a single column in a table and then use the FX button on the TABLE LAYOUT tab
