We saw that SUBTOTAL ignored rows hidden by a filter. The SUBTOTAL function has the ability to add up only the visible cells. The formats can then be used to filter the list and have SUBTOTAL add up the filtered values. You can either manually format the cells or you can use conditional formatting. Many people are unaware that you can filter by colour in Excel. Had you used =SUM(B2:B9) it would display 2447 no matter what filter is applied. This calculation automatically adjusts to sum just the visible cells as the filters change. The formula in cell B11 is =SUBTOTAL(9,B2:B9). The table on the right of Figure 3 has been filtered by WA and NSW. SUBTOTAL does something unusual when working with a filtered list or table. It doesn’t have regions, so it has a single entry.Ĭolumn E handled the inserted row perfectly, but totals for columns C and D are wrong and both will need modification.
In Figure 2 I have inserted a row for Tasmania. If a single row is added with a value and no subtotal, then the grand total will be wrong. This assumes that there are value rows followed by a subtotal row for each section. Issues with the grand total rowĬolumn C – if a new section is added to the data you must remember to add it to the grand total calculation at the bottom.Ĭolumn D – the SUM function adds up the whole range and divides by two.
Column E uses the SUBTOTAL function in rows 6, 11 and 12. Columns C and D have SUM functions in rows 6 and 11. The values in columns C, D and E are the same. Since the SUBTOTAL function ignores all other SUBTOTAL functions within its range, it means the grand total formula is much simpler than if you use a SUM function. Any SUBTOTAL functions within these ranges will be ignored. Ref1, Ref2 – range(s) to perform the calculation on. In most cases, 9 or 109 are used as they represent the SUM function in Excel. Syntax: SUBTOTAL(Function _Num, Ref1, )įunction _Num – a number that specifies the function to use in the calculation. Leave a comment below, or post it to Excel’s official suggestion box.Most people use the SUM function when subtotalling, however the grand total calculation can be an issue when rows are inserted within the range. What other measures would you like to see available in the status bar? (From what I’ve tried, I was not able to get this working as an add-in - and I’d be happy to be proven wrong.)
#SHOW SUM IN EXCEL ON BOTTOM CODE#
You should then see these extra statistics on the left-hand side of the status bar:ĭownsides? You must include this VBA code in any individual workbook for which you want to get these measures. Copy and paste the code into the event procedure as shown below:.From the VBA Editor, double-click the worksheet on the project explorer that you want to run the code from.Next, we tell Excel to run the code when a range is selected: Calculations of skewness and kurtosis are not possible (or meaningful) without at least a handful of numbers, so the code only displays these statistics if at least 5 cells are populated. This code calculates our four statistics and tells Excel what to display upon calculation. Copy and paste the below code into the module:.Insert a module into the active workbook by clicking the Insert menu and selecting Module.To add these statistics to the status bar, we write these functions in a VBA module, then use the Worksheet Selection event to run when a selection is made:
Please note that I am an affiliate of Jon’s course and receive a portion of any sales generated with the above links. Jon is a good guy and an excellent VBA teacher. While these are not included in the Customize Status Bar menu, we’ll find a way using VBA. Moreover, I would like some more information on the statistical properties of our range, including skewness, kurtosis, and standard deviation. In particular, while the status bar gives us a numerical count of our range, I want to more precisely see the number of blanks in a range. While our current status bar is an improvement and certainly a time-saver, I’d like to be able to see a few more statistics with the mere highlight of a mouse. Recently, I learned that’s not the end to the status bar’s magic. Not seeing min/max statistics on your status bar? Simply right click to “Customize Status Bar” and select other statistics along with some other options. Indeed, the range’s sum along with a few other statistics were displayed in what is called the status bar.
As I went to type in =SUM( to add a range of cells, my coworker stopped me: “You don’t need to do that - just look down to your right!” One of the first days at a new job, I was sitting with a coworker going over a spreadsheet. Special thanks to Alan at Computergaga for inspiration on this post.