Horizontal Subtotals?

[mgirvin]

Dear Smartest Excelers In The World,

The Subtotal feature works vertically. Is there a way to do it horizontally?

 

[lenze]

Can you post some sample data so we can see how the Subtotals might be arranged?
lenze

 

[Beate Schmitz]

Hello mgirvin,

for my proposal you need a helprow and you have to define a name as follows:

Here is the list with all columns visible:

Excel Workbook
	A	B	C	D	E	F
1	5	5	5	5	5	25
2	4	4	4	4	4	Helprow
Sheet

#VALUE!

</td></tr></table></td></tr></table>

After hiding/unhiding colums the result in cell F1 is refreshed after pressing F9:

Excel Workbook
	A	B	E	F
1	5	5	5	15
2	4	4	4	Helprow
Sheet

#VALUE!

</td></tr></table></td></tr></table>

May be you have to change the words "Zelle" to "cell", and "Breite" to "Width" in the formula defining the name. I cannot show that because I'm using German Excel.

 

[mgirvin]

Dear lenze and Beate Schmitz,

Sorry I did not post back sooner – I asked the question and then was delayed in getting back to check for an answer. Actually, all I was asking was if there was a way to get the Subtotal feature to work horizontally.

As for data setup, I have field names in F1:F3, data in cells G1:AL4, row one is sorted, and row 3 has the numbers to sum. So in row 4 for Subtotals I have used the formula:

=IF(G1<>H1,SUMPRODUCT($G$3:$AL$3,--($G$1:$AL$1=G1)),"")

And this works fine, I was just wondering if Subtotals feature would automate the process.

As for the CELL &"width" formula setup, that is quite a clever trick. I have not seen CELL and “width” used in such a way before. That is a good one for my Excel Toolbox!

 

[mgirvin]

... So in row 4 for Subtotals I have used the formula:

=IF(G1<>H1,SUMPRODUCT($G$3:$AL$3,--($G$1:$AL$1=G1)),"")

I meant to say:

... So in row 4 for Subtotals I have used the formula:

=IF(G1<>H1,SUMIF($G$1:$AL$1,G1,$G$3:$AL$3),"")