SUM, SUMIF, SUMIFS, SUM Indirect –

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.

It can take one of two forms:
=sum(range) e.g. =sum(b2:b7)
=sum(individual cells) e.g. =sum(b2,b7,b9)

Another example would be =SUM(b2:e2)
This would add up the contents of 4 cells

Another example would be =SUM(b2:b10,b14,b20:b25)
Here we are mixing the : and , symbols within the brackets.
: is range and , is discrete

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 https://www.jplcomputer.co.uk/microsoft-excel-training.html

For prices and availability please phone 07903 840105 or email us johnlegge@jplcomputer.co.uk

If you liked our hint and tip post on SUM, SUMIF, SUMIFS, SUM Indirect, you might like to take a look at our last hint and tip on Excel shortcuts!

The post Excel Hints and Tips! Looking at SUM, SUMIF, SUMIFS, SUM Indirect appeared first on JPL Computer Services.

Source: JPL