Spy Journal 3.0 Filtering the web for you

The Subtotal function is commonly used when creating Subtotals on a table with the Data | Subtotals Menu command. However it can be just as easily used by writing the formula and has some powerful uses.
The syntax is SUBTOTAL(function_num,ref1,ref2,...)
The function numbers allow you to use any of the following functions in the subtotal:

The most common is of course the SUM function.
When and why should you use SUBTOTAL and not just the SUM function on its own.

The SUBTOTAL function has some powerful abilities that the commonly used SUM function doesn't. For example when summing a list of cells that contains hidden rows or columns the SUM function will include them. However the SUBTOTAL function only operates on visible cells. The values of the hidden cells are excluded from the total. This is especially useful when filtering a list if you want to see the total of the visible (or filtered) cells.
Note by default, if you have a filtered list of values and you double click the sum (sigma) icon on the toolbar after you select a cell below the bottom of the filtered range Excel will not use a SUM formula but automatically use a SUBTOTAL function.

The SUBTOTAL function ignores other subtotals within the range. So if you have several subtotals and a grand total, the grand total formula can be the subtotal of the entire range that includes the subtotals and it will ignore them.

Links to my other Articles about Subtotals including using outlining.

http://www.spyjournal.biz/exceltips/2005/03/creating-subtotals-in-excel.html

http://www.spyjournal.biz/data_filters_in_Excel_2003_and_2007

Today I had an email request:

I wasn't sure how to post, or join so I'm sending my question to you this way. I cannot figure out how to remove the #N/A error in LOOKUP. If the cells are blank it returns the #N/A error - I'd like to return either a blank or 0 instead. How do I do that? Her is my formula. (I'm sure you can tell by my formula I'm a rookie at this.) Any help is appreciated. Thanks!

=SUM((LOOKUP(B6,{1,2,3,4,5},{40,30,20,10,0}))+(LOOKUP(C6,{1,2,3,4,5},{40,30,20,10,0})+(LOOKUP(D6,{1,2,3,4,5},{40,30,20,10,0})+(LOOKUP(E6,{1,2,3,4,5},{40,30,20,10,0})+(LOOKUP(F6,{1,2,3,4,5},{40,30,20,10,0}))))))

I have written a post on this before when I explained the ISNA function.

In this case I will break down the first component of the formula and show you how to insert it.

First of all understand that because this formula is summing components that are lookups, if any one of them returns an error than the overall sum will be an error even if the rest is not an error.

So to fix =LOOKUP(B6,{1,2,3,4,5},{40,30,20,10,0}) when it displays #N/A (eg if B6 is less than 1) we would use this function:

=IF(ISNA(LOOKUP(B6,{1,2,3,4,5},{40,30,20,10,0}))=TRUE,0,LOOKUP(B6,{1,2,3,4,5},{40,30,20,10,0}))

The same concept could be used for each lookup component. In this case the formula would become very involved so there might be easier ways to do it. Sometimes breaking the individual components our into individual cells and then summing them is a way to give more visible results – and easily see which component is causing the error.

So Lois – i hope that helps – and thanks for liking us on Facebook!

The IF function is a very useful logical tool. I have written an explanation on the IF Function before so I wont repeat that here. There are a number of other posts also that incorporate the use of the IF Function.

What I want to do is highlight a useful way to use the IF function.

I use it a lot to compare lists of data. today I had been sent some data in a spread sheet that contained email addresses. I had to create a mail out to those email addresses and when i did that I received a number of bounce backs for incorrect email addresses. I then received a second email from the original person containing a revised spread sheet with the corrections to email addresses. given that there were a large number of addresses and only a few changes I didn’t want to painstakingly work my way through the list looking for changes. Instead I copied the new list beside the old list and then wrote an if statement to compare the original with the revised. It is very simple and uses a 1 or 0 result. You could use anything there including TRUE or FALSE or “OK” and “ERROR” etc. Then a simple filter showed me the incorrect ones and the fixes i needed to apply in my mailing list.

Obviously I have obfuscated the email addresses in this example for privacy reasons.

The actual IF statement is very simple. In Cell C1 I wrote =IF(B2=A2,0,1). Filling it down is as simple as double-clicking the right hand bottom corner of the cell.

Then I added a simple filter to hide the correct results and only display those with differences.

I use this technique many times a week in comparing data lists.

While looking at previous posts to choose a different function for this weeks post I noticed that I have never written a post specifically about the most commonly used function in Excel, the SUM function.

So today’s post remedies that. Note this post is based on Excel 2010. Most of this is still relevant in Excel 2003 and Excel 2007 is largely the same as 2010. The main difference is the ribbon references in Excel 2007/2010 vs the toolbar in 2003.

First the Official Excel Help information.

Description

The SUM function adds all the numbers that you specify as arguments. Each argument can be a range, a cell reference, an array, a constant, a formula, or the result from another function. For example, SUM(A1:A5) adds all the numbers that are contained in cells A1 through A5. For another example, SUM(A1, A3, A5) adds the numbers that are contained in cells A1, A3, and A5.

Syntax

SUM(number1,[number2],...])
The SUM function syntax has the following arguments:
number1    Required. The first number argument that you want to add.
number2,,...    Optional. Number arguments 2 to 255 that you want to add.

Remarks

If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored.
If any arguments are error values, or if any arguments are text that cannot be translated into numbers, Excel displays an error.