formula Subtotal 109 when hiding columns does not work in excel

Question

I have formulas in column B which subtotal every other column starting with column E. So column E, G, I, K, M, O, Q, S, and eventually more. The formula in row 4 is

=SUBTOTAL(109,E4,G4,I4,K4,M4,O4,Q4,S4)

When I hide one of these columns the total in column B does not adjust to reduce the value.

Any ideas what might be causing this. It seems that this should be straightforward. I've used subtotal before and it works in my other workbooks. This is a simple work book or so it seems. 55 rows on the sheet with 52 of them having this formula. None of them work. This will be simple I'm sure and I will be embarrased but I have tried a number of things unsuccessfully.

From the help _...hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal_ — chris neilsen, Dec 20 '15 at 22:52
@chrisneilsen Thank you Chris I overlooked the obvious but the answer below from Dirk did get me where I wanted to go so it was a win win. — KG869, Dec 21 '15 at 15:22

score 2 · Answer 1 · answered Dec 21 '15 at 00:14

For SUBTOTAL pls read This

The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.

The only non-VBA way to do this (which I know) is using CELL like this:

=SUM(E4*(CELL("width",E4)>0),G4*(CELL("width",G4)>0),I4*(CELL("width",I4)>0),K4*(CELL("width",K4)>0),M4*(CELL("width",M4)>0),O4*(CELL("width",O4)>0),Q4*(CELL("width",P4)>0),S4*(CELL("width",S4)>0))

And the downside is, that you need to do any real action to recalculate (while subtotal does a recalculation by hiding a row, CELL does not). Simply enter a cell and hit enter (or just del while selecting an empty cell) or hit "calculate now" in the "formulas" tab.

It uses the behavior that hidden rows return a width of 0 (same for rows and height), but it checks for the whole column here (hiding a cell by row, doesn't change the width).

Also you can't use it in an array like this and the formula also doesn't look pretty nice. But at least, you can simply copy it down.

This is perfect and works well. It does take a bit of formula work but will be a good alternative to complete rework of the spreadsheet. Thank you very much. — KG869, Dec 21 '15 at 14:04

score 1 · Answer 2 · edited Aug 31 '16 at 16:38

I suggesting to create helper row such as E12 - S12

=CELL("Width",E4)

put this code in every single column and hide entire row

Then in in sum cell, let's say T4 write this code

=SUMIF($E$12:$S$12,">0",E4:S4)

The only problem with this formula is, that particular event "Hide", "Unhide" Is not registered as event and sum cells are not recalculated. This could be fixed with VBA and UIeditor.

You will need UIeditor to add custom ribbons for your sheet to catch this specific event such as hide / unhide columns to do that you need editor downloadable here: http://www.rondebruin.nl/win/s2/win001.htm

Then add this code for your sheet:

<customUI  xmlns="http://schemas.microsoft.com/office/2006/01/customui" >
<commands >
    <command 
        idMso="ColumnsHide"
        onAction="Column_Hide_Macro"/>
    <command 
        idMso="ColumnsUnhide"
        onAction="Column_UnHide_Macro"/>
</commands >

So now when someone hit hide or unhide column macros "Column_Hide_Macro" or "Column_UnHide_Macro" are activated

Then add into your spreadsheet this

Sub column_hide_Macro(control As IRibbonControl, ByRef cancelDefault)
    cancelDefault = False
    alertTime = Now + TimeSerial(0, 0, 0.1)
    Application.OnTime alertTime, "reCalc"
End Sub
Sub column_unhide_Macro(control As IRibbonControl, ByRef cancelDefault)
    cancelDefault = False
    alertTime = Now + TimeSerial(0, 0, 0.1)
    Application.OnTime alertTime, "reCalc"
End Sub
Sub reCalc()
    Application.CalculateFullRebuild
End Sub

Add this code into the module not sheet or workbook code!

Now you should get sheet recalculated every single time when you hide or unhide column.

Cheers...

score 0 · Answer 3 · edited Jul 06 '17 at 21:38

0

In row 12 for example put "H" in every column you do not want to sum. The formula in cell B4 then is

=SUMIFS(E4:S4,E12:S12,"<>H")

edited Jul 06 '17 at 21:38

Floern

33,559
24
104
119

answered Jul 06 '17 at 21:02

Davo B

1
1

score -1 · Answer 4 · answered Dec 20 '15 at 22:36

-1

SUBTOTAL filters hidden rows, not hidden columns or cells.

This behavior is closely tied to the auto-filter feature that allows hiding rows using a dropdown control.

The simplest solution is to restructure your sheet and use hidden rows.

answered Dec 20 '15 at 22:36

Amit

45,440
9
78
110

@ Amit In essence yes it is simplest. I now see that this is the case. Unfortunately in this application column A is a rather lengthy description field and doesn't bode well in clean appearance being placed in multiple column headings even with wrap. Thank you for your time to respond. – KG869 Dec 21 '15 at 14:05

formula Subtotal 109 when hiding columns does not work in excel

4 Answers4