Subtotal for column range ( not row range )

[Rolly_Sefu]

Hello,



I have a table that is growing with each day to the RIGHT. Column data are numbers


And at one point i want to hide some columns and make a sum of the columns that remain.


But Subtotal only works with rows.


Is there a way to make: =SUBTOTAL(109;A2:F2) - this one is not working


Thank you

 

[r1998]

Hello Rolly_Sefu
try this

=SUM(109,A2:F2)

 

[jtakw]

Hello,

But Subtotal only works with rows.

Is there a way to make: =SUBTOTAL(109;A2:F2) - this one is not working

Thank you

Hi,

I don't think that's true, SUBTOTAL works for Rows as Well as Columns (may be it's because of the "semicolon"?)

Otherwise, try using SUM as suggested by r1998, but take out the "109"

so just =SUM(A2:F2)

 

[Rolly_Sefu]

Hello


The sum works just fine if all column are shown.


But then i hide a column, the sum result does not change, and its the same situation with the subtotal.

Any other ideas ?

 

[Fluff]

One option, with a helper row

Excel 2013 32 bit
	F	G	H	I	J
1	9	8	6	8
2	51.750185	-0.32129	515981	207054	723086.4
Update

Cell Formulas
Range	Formula
F1	=CELL("width",F1)
G1	=CELL("width",G1)
H1	=CELL("width",H1)
I1	=CELL("width",I1)
J2	=SUMIF(F1:I1,">0",F2:I2)

 

[Fluff]

Excel 2013 32 bit
	F	G	I	J
1	9	8	8
2	51.750185	-0.32129	207054	207105.4
Update

But you will need to hit F9 as this will not recalc automatically

 

[Fluff]

Now that my browser has stopped playing silly buggers.
Found the above idea here https://wmfexcel.com/2015/08/29/a-trick-to-sum-visible-columns-only-without-vba/