SUM SUMIF SUMIFS SUM Indirect – the many faces of SUM!
This week’s blog is all about that most favourite of functions in Excel. The SUM statement which you will all be aware of is the cornerstone of any Excel workbook system. In this post, we are looking at SUM, SUMIF, SUMIFS, SUM Indirect and how they could be applied in your workbook. Please read these notes in conjunction with the attached workbook (SUM, SUMIF, SUMIFS, SUM Indirect workbook) to see example of each in practice.
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?