Excel functions – SUM, SUMIF and SUMIF Indirect

As you will all be aware the SUM function is the cornerstone of any Excel workbook system.

It can take two forms within your spreadsheet:

=sum(range)  e.g. =sum(b2:b7)

=sum(individual cells)  e.g. =sum(b2,b7,b9)

There are also two enhanced sum functions which are: SUMIF and SUM with INDIRECT

=SUMIF(b2:b9,”>30″)  only add up these cells IF a cell value is greater than 30

=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!!

Hope you gain SUM use out of these tips!

Excel functions – ‘what if’ scenarios

This little used feature is one that could be used in businesses but it can also be used at home too. This feature is used alongside a number of financial functions within Excel.

It allows the ‘what if’ scenarios to be built particularly around functions like:

  • FV (Financial Value)
  • PMT (Payment)
  • Rate (Interest rate)

You basically select the variables of the function and calculate the end result.

So in the case of FV you may wish to calculate the end figure on a savings plan using different periods of time, interest rates and amounts of money per period of time.

You can create a neat Summarised Scenario of various choices where the variables change.

So in business, Scenarios could be used for asset depreciation and in the home for mortgage or loan repayments.

This is covered more in our Master Class Bronze course in Excel.

If you want to know more about any of the functions above, take a look on our website here. We have 5 levels of Excel courses, so there will no doubt be at least one course for you! If you have a a query, feel free to send us an email on johnlege@jplcomputer.co.uk.

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