Home » Excel Time Saving Features » Functions

Category: Functions

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

Sharyn Baines


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

Sum only visible rows when Filtering data in Excel

Sharyn Baines

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.

Continue Reading
Sharyn Baines

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

Sharyn Baines

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

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