Subtotal Function

[schielrn]

I thought this may be a good place for this. I came across a co-workers spreadsheet and they had it set up where they were doing subtotals of a column, but for some reason were also using subtotals for the row totals, but were never hiding or unhiding the rows. BUT the weird thing I noticed is that the subtotal did not work on rows that had subtotals in them as well (See G1 for the total of that column?)

Excel Workbook
	A	B	C	D	E	F	G
1		5212	7343	4426	4414	4734	0
2	Location	Jan	Feb	March	April	May	YTD Total
3	1	517	933	633	318	775	3176
4	2	622	879	763	222	731	3217
5	3	62	0	131	303	624	1120
6	4	840	611	606	99	219	2375
7	5	466	901	141	832	594	2934
8	1	111	956	147	571	456	2241
9	4	588	555	297	197	345	1982
10	5	607	971	944	574	32	3128
11	2	496	614	166	849	659	2784
12	6	903	923	598	449	299	3172
Sheet1

Cell Formulas
Range	Formula
B1	=SUBTOTAL(9,B3:B12)
C1	=SUBTOTAL(9,C3:C12)
D1	=SUBTOTAL(9,D3:D12)
E1	=SUBTOTAL(9,E3:E12)
F1	=SUBTOTAL(9,F3:F12)
G1	=SUBTOTAL(9,G3:G12)
G3	=SUBTOTAL(9,B3:F3)
G4	=SUBTOTAL(9,B4:F4)
G5	=SUBTOTAL(9,B5:F5)
G6	=SUBTOTAL(9,B6:F6)
G7	=SUBTOTAL(9,B7:F7)
G8	=SUBTOTAL(9,B8:F8)
G9	=SUBTOTAL(9,B9:F9)
G10	=SUBTOTAL(9,B10:F10)
G11	=SUBTOTAL(9,B11:F11)
G12	=SUBTOTAL(9,B12:F12)

Can anyone explain why this phenomenon is happening? It seemed really strange to me.

 

[el_ja]

Well excel help says
"If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting."

So probably cause of that

 

[AlphaFrog]

The SUBTOTAL function ignores results from other SUBTOTAL functions.

Excel Workbook
	A	B	C	D	E
1	1	1	1	3	3
2	1
3	1
4	3
5	3
Sheet

 

[xenou]

How funny - I've never seen subtotals of a row before! That's kind of interesting. Note: one other feature of subtotaling that's also kind of neat (but probably rarely used) is the extra arguments for ignoring hidden rows. I've probably encountered cases where I might have employed this feature (but of course forgot about it at the time )

 

[schielrn]

Well excel help says
"If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting."

So probably cause of that

I never think to look there because I am use to the vba help, which is of no help.

How funny - I've never seen subtotals of a row before! That's kind of interesting. Note: one other feature of subtotaling that's also kind of neat (but probably rarely used) is the extra arguments for ignoring hidden rows. I've probably encountered cases where I might have employed this feature (but of course forgot about it at the time :wink: )

Yes I have used the 109 feature quite a few times, but don't always remember it and then come back to something and wonder why I didn't put it in there in the first place.

Thanks for the answers. It makes me wonder why it was built to ignore the answers of other subtotals? I'm not sure I can always buy the double counting argument.

 

[SydneyGeek]

SUBTOTAL has a few tricks up its sleeve, some of them annoying.
I recently got caught with a table that always left the last row visible, regardless of what I filtered by. Turns out that one column had a SUBTOTAL where a SUM would have worked too. Changing to SUM made the filter work properly again.

Denis

 

[jasonb75]

SUBTOTAL has a few tricks up its sleeve, some of them annoying.
I recently got caught with a table that always left the last row visible, regardless of what I filtered by. Turns out that one column had a SUBTOTAL where a SUM would have worked too. Changing to SUM made the filter work properly again.

Denis

That problem came up a few weeks back with a suggestion I made in another thread, think it was VoG pointed out that it can be prevented if the subtotal column is excluded from the filter range, though not much use if you need to filter on subtotal.