SUBTOTAL vs SUM excel formula

Excel "SUBTOTAL" formula is one of the exciting excel formulas that you may come across.

The first thing you should know, is that SUBTOTAL doesn't just add up numbers like SUM, it can do eleven different calculations.

Syntax

SUBTOTAL(function_num,ref1,[ref2],...])

=SUBTOTAL(9,A1:A2) is same as =SUM(A1:A2), however…

SUBTOTALs in SUBTOTAL range are ignored

One of the main issues that excel users face while working with large financial data, is double counting. This occurs when they have to deal with a lot of SUM formulas in a row. SUBTOTAL resolves the issues with ease by ignoring any cells that have subtotals.

Hidden rows can be ignored

One possible limitation of SUM, is that it adds up all of the values within a range, even if you were to hide one or more rows. However, SUBTOTAL gives you the option to add hidden rows (same as SUM formula aka function number 9) or ignore hidden rows (function number 109).

SUM of filtered table

If you use SUM to add values in a filtered table, it will add all of the values within the table instead of the filtered values. Again, SUBTOTAL excel formula simplifies things by adding only what has been filtered. It is compatible with both the 9 and 109 function numbers.

Automatic SUBTOTALs feature

Under the excel "Data" menu, there is a Subtotal button. It is a feature that automatically inserts subtotals within related data. Highlight your table and click on the Subtotal button. You can then specify which group needs subtotalled.

Even superheroes have their limitations,

In this case the 109 function is not capable of processing horizontal data. For instance, =SUBTOTAL(109,A1:F1) will add all values in a range, even if any column is hidden.

MS EXCEL: ISERROR FUNCTION

Learn how to use the Excel ISERROR function with syntax and examples.

DESCRIPTION

The Microsoft Excel ISERROR function can be used to check for error values.

SYNTAX

The syntax for the Microsoft Excel ISERROR function is:

ISERROR( value )

PARAMETERS OR ARGUMENTS

value is the value that you want to test. If value is an error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL), this function will return TRUE. Otherwise, it will return FALSE.

APPLIES TO

The ISERROR function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

TYPE OF EXCEL FUNCTION

The ISERROR function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)
• VBA function (VBA)

EXAMPLE (AS WORKSHEET FUNCTION)

Let's look at some Excel ISERROR function examples and explore how you would use the ISERROR function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel ISERROR examples would return:

=ISERROR(A1)	would return TRUE
=ISERROR(A2)	would return TRUE
=ISERROR(A3)	would return TRUE
=ISERROR(A4)	would return FALSE
=ISERROR("www.excelconcepts.blogspotblog.com")	would return FALSE
=ISERROR(3/0)	would return TRUE

EXAMPLE (AS VBA FUNCTION)

The ISERROR function can also be used in VBA code in Microsoft Excel.
Let's look at some Excel ISERROR function examples and explore how you would use the ISERROR function in Excel VBA code:

Dim LReturnValue as Boolean

LReturnValue = IsError(CustomFunction())

In this example, the variable called LReturnValue would now contain whether the call to the CustomFunction resulted in an error.

FREQUENTLY ASKED QUESTIONS

Question: Can you give me specific examples of when and how the ISERROR function is used. Specifically, in a worksheet why would I use this function instead of just running down a column or across a row to look for the errors?
Answer: Often times your spreadsheet contains a large amount of formulas which will not properly calculate when an error is encountered. The ISERROR function, in combination with the If function, can be used to default a cell's value when an error is occurred. This allows your formulas to evaluate properly without your intervention.
For example, you may encounter a scenario below:



Instead of using the formula:

=B4/C4

You could use the ISERROR function as follows:

=IF(ISERROR(B4/C4),0,B4/C4)

In this case, the ISERROR function would allow you to return a 0, when an error was encounter such as a "divide by 0 error". Now all of your formulas will still work.