SUM SUMIF SUMIFS SUM Indirect | Excel Hints and Tips

SUM SUMIF SUMIFS SUM Indirect – the many faces of SUM!

SUM SUMIF SUMIFS SUM Indirect: Excel iconThis 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) 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



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


Why not take a look at our previous post on 10 Excel shortcuts?

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We use cookies to give you the best online experience.

Please let us know if you agree to all of these cookies.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy