Recently I got a workbook with this formula:
I’m sure you’ve seen a few formulas like this. I know I have. I may have even made one or two. There is a better way, however. Usually a formula like this means there are several lists in the column with subtotals and this formula returns a grand total. The problem is when you want to add a new list or item and include it in the total. It’s not hard to add a +F62 to the above formula, but if you forget to do it, it’s not always obvious that the formula is wrong.
I changed the above using the SUBTOTAL function (with 9 as the first argument because I’m summing). One really nice feature of SUBTOTAL is that it ignores any cells that have a SUBTOTAL function in them. I can SUBTOTAL the whole range, and as long as I’ve used SUBTOTALs within the range, I don’t have to worry about double counting. If I add a new item in the middle, the SUBTOTAL range will adjust to accommodate.
Here’s an example of the old way:
Here’s the same example using SUBTOTAL:
There is another way I’ve seen this done, and when you realize that SUBTOTAL – although neat – requires “specialized” knowledge about its operation, perhaps it’s not notably worse:
Grand Total =Sum(B2:B22)/2
Subtotal, however, retakes the advantage when there’s more than one level of cost subtotaling.
Hi Dick –
The tip I picked up so so long ago was
Grand Total: = SUM(B2:B22)/2
Your way looks better.
Happy New Year.
…Michael
One other trick to use is realizing that subtotals are only for display, not for further calculations. So you could use formulas like =TEXT(SUM(B2:B4),”#,##0?) in B5 (and set Right horizontal alignment). The grand total would be given by =SUM(B2:B20).
That’s one use of subtotals which I’ve only used in one report in the past few years. My biggest use is to sum or count items in a filtered list. Subtotal only includes items in visible rows. I find this great for adhoc reports.
What is not widely known about the Subtotal function is that apart from accepting parameters 1,2,3…9, 10 and 11 it also accepts parameters 101, 102…109..111 which lets you perform the same operations ingnoring hidden (not filtered) rows/columns
Sam
Also, try using OFFSET in the sum/subtotal cells, e.g.
=SUBTOTAL(9,B18:OFFSET(B21,-1,0)) instead of =SUBTOTAL(9,B18:B20). These types of reports always have rows inserted at some point.
Nicely done, Dick.
But I must thank you for posting on this topic a day after I did. :-)
Great minds think alike? LOL!
Don’t take me seriously. I just HAD to comment about this. I mean, of all the possible topics, what are the odds that this would happen? At least we’re both pointing out good things to our readers.
Have fun.
Nice blog Sandy. I went back and read the first post, but I still don’t know what Monarch is. Can you give me a one-sentence summary of what it is or does?
Also, I’ve subscribed to your blog so I can beat you to the punch next time. :)
Thanks Dick. You’ve been a real inspiration for quite some time.
Hmm, one sentence huh…
Monarch is kind of like a universal data translator for non-programmers that just happens to specialize in extracting data from electronic files like report files (ASCII or ANSI *.txt,*.prn files), PDF, HTML, or log files.
It allows you to turn static reports into dynamic data. Imagine taking something like a 10,000 page Oracle report and getting raw data out of the report and into Excel, where you can analyze to your heart’s content, in mere seconds. 100% accurately too. Every time.
The Pro version also allows you to extract data from database sources like spreadsheet files like Excel or Lotus, databases like Access, dBase or Paradox, or any ODBC compliant database, like Oracle.
All without any programming, or even knowledge of programming.
Of course, if you do have programming skills, Monarch offers a COM interface, so you can automate your repetitive tasks.
I’ve built custom reporting systems driven by Excel, with VBA, that call on Monarch to extract data from about 30 reports files, integrate data from other xls files, and automatically generate weekly regional reports for hundreds of users across the country.
Every Monday I create eight regional reports, encapsulating 100’s of MB of raw data. It takes about 45 minutes to produce all of the files with Monarch and Excel. On a run-of-the-mill laptop. Nothing fancy.
Once the report files have been downloaded, it’s a completely hands-free reporting system. And I couldn’t possibly do it without Monarch.
I’ve been using Monarch for about five years now. I invite anybody who works with any significant amount of data to just give it a try. You’ll wish you’d had it years ago.
Thanks again for the compliment Dick. I look forward to some friendly competition this year. :-)
Sam: The 101, 102 arguments are enew to Excel 2007 IIRC. Their old counterparts ignore hidden rows when filtered, the new ones also ignore manually hidden rows.
This is my solution. Select the cells where you want your subtotals. Use Goto special, empty cells and press the autosum button. Then select the cell where you want your total and again press the autosum button.
This works with continuous areas. If not continuous, use Edit-Replace to change “SUM(” with “SUBTOTAL(,”
Regards
Per
Jan Karel
I have Excel 2003…101 102…etc ignore manually hidden rows in 2003 as well
Regards
Sam
Grand Total =Sum(B2:B22)/2 only works when every value is already included in a subtotal. Otherwise, a value that is not in a subtotal is divided by 2. Cells B6, B11, B17 and B22 don’t appear in the subtotals so I coudl enter a number there and mess up that math until I also add a subtotal. It may be good business practice to subtotal everything, but on most spreadsheets, if I have an item that doesn’t consolidate with any other items, I don’t subtotal it.
Neat tip! Thanks…