Category: Functions

20/06/2018

Use the SUMIF function to total only the cells that match your requirement

Use the SUMIF function to total only the cells that match your requirement.

For example, if you wanted to know the total sales made by one of your sales team members you can use the SUMIF function to only add to the total the sales made by a certain member. Gold! This function saves you a lot of time.

Prior to learning this function most people filter their data based on their requirement, e.g. the team member’s name, and then copy and paste the info into another worksheet. Once in the new worksheet they then use the SUM function to create the total. Continue Reading

20/06/2018

Insert an Excel function into your workbook

Learn how to insert an Excel function into your workbook.

Excel comes equipped with a large number of pre-defined formulas. These are known as functions.

There is a great amount of functions available in Excel. Each created for a specific purpose. There are functions specific to Maths, Science, Finance, and Engineering to name a few. Continue Reading

03/06/2018

Sum only visible rows when Filtering data in Excel

A common question I get asked when I’m teaching people how to filter data is “how do I sum only the visible rows when filtering data in Excel”?

It’s very common for people to try creating a SUM total at the bottom of a list and then filter the data hoping that the total will change to reflect the SUM of the data displayed. However, the total doesn’t update. It still shows the SUM of the entire list.

You can definitely use the AutoSum button to instantly create a total that will update as you filter your data. Here’s how you do it.

13/04/2015

Excel – How do I sum a range of cells that include #N/A or #DIV/0! errors?

I think most of us will agree that error values can be helpful, and sometimes extremely unhelpful. For example, the #N/A error (Value Not Available error) is very helpful when cross-referencing two lists using the VLOOKUP function. If Excel can’t find a lookup value the #N/A error makes it very obvious.

However if you are wanting to total a range that includes #N/A errors all of a sudden they aren’t so special any more. Continue Reading

26/03/2014

Excel – Generating random numbers

PROBLEM: Generating random numbers.

SOLUTION: Let Excel do it for you!

12/03/2014

Excel – Hide errors in a worksheet

PROBLEM: how do I hide errors in a worksheet? For example the #DIV/0 error or the #N/A error. Not only do they look not so great on the worksheet, they can cause other functions and formulas that include the cell holding the error to bring back an error as well (sigh).

SOLUTION: Start using the IFERROR function to hide the errors. Not only will it hide the errors in your workbook, your other calculations will no longer show errors. Read more