SUM SUMIF SUMIFS SUM Indirect – the many faces of SUM!
SUM
It can take one of two forms:
=sum(range) e.g. =sum(b2:b7) or another example would be =SUM(b2:e2) – this would add up the contents of 4 cells
=sum(individual cells) e.g. =sum(b2,b7,b9) or another example would be =SUM(b2:b10,b14,b20:b25) – here we are mixing the : and , symbols within the brackets.
The symbols used in the SUM formula are as follows:
- : is range
- , is discrete or individual cells
SUMIF and SUMIF with INDIRECT
Two enhanced SUM functions are SUMIF and SUM with INDIRECT
An example would be =SUMIF(b2:b9,”>30″) This function only add up these cells IF a cell value is greater than 30
Another example is =SUM(INDIRECT(“b1”):B4) This neat nested function allows for the fixing of b1 so that if you insert a row above the current row 1, that row will still be used in SUM!!
A further use of SUMIF can be seen in the attached workbook using 3 arguments not 2.
SUMIFS is a development of the SUMIF whereby you have multiple criteria needing to be met before a SUM of numbers is added. Again look at the attached example for the work through.
On our Excel courses, we cover a whole range of Excel functions such as SUM, AVERAGE, COUNT, IF, VLOOKUP, AND, OR, CONCATENATE and many others.
If you would like to attend a course on 1 of 4 levels please visit our web site page for course content and details here.
For prices and availability please phone 07903 840105 or email us johnlegge@jplcomputer.co.uk
Why not take a look at our previous post on 10 Excel shortcuts?