Done In 60 Seconds – Cumulative Sums

By
16 May at 14:00

We often have an ordered list of items each with an assigned value such as revenue or cost which we need to analyse and understand. It is sometimes important to know how these numbers are adding up in order that we can be alerted to business limits (budgets, deadlines etc,) being approached or reached.

 

Suppose we have some monthly marketing costs in a speadsheet. And suppose we want to know how much we have cumulatively spent at any given month of the year, so that we can see, for example, at what point we hit a certain budget level eg. £500k. There are a number of unwieldy ways of doing this (example, put B2 into C2 and then =C2+B3 into C3 etc) but here’s the best.

Type =SUM($B$3:B3) into cell C3

What this is doing is asking Excel to add up all the cells starting from B3 down to the current row (in the first case this is also B3). So in cell C4 you should have =SUM($B$3:B4). Pay special attention to the dollar signs – you will not be able to copy the formula down if these are wrong! Copying the formula down for the remaining 10 lines will give (see yellow):

This trick also calculates cumulative percentages nicely (on the right, in blue).


Excel with Business

This short lesson is from unit 11 (of 32) of Excel with Business’s online Excel-training course – see excelwithbusiness.com/Home.aspx for more details.