Excel pivot-tables subtotals

7
votes

I am trying to build a pivot table in Excel to summarize some data, like

enter image description here

I want columns A and B to be in compact form and column C to be outlined; also I need subtotals for the B column. Letting subtotals appear on the last row, everything works fine

enter image description here

But when I go to the B field contextual menu, select "Field Settings/Layout & Print" and select "Display subtotals at the top of each group" subtotals break down: some do not appear (for the "b/a" configuration) and some others give the wrong result (60 for the "a/a" combination).

enter image description here

Is there a way out of this? I am using Excel 2010 but tested the same file on Excel 2007 and the same issue seems to arise.

Any advice would be greatly appreciated.

1
excelpivot-table
If you press F9 key does the value update? If so you may have calculate set to "Manual". Try Tools > Options > Calculation > set to "automatic" – venkatKA
I tried refreshing but it does not help. There seems to be a bug with data in some particular configuration; I just edited the post to add a data sample where the problem arises. – caseroR
How do you use the SUBTOTAL function? like =SUBTOTAL(____?) – venkatKA
I am letting the pivot-table to do subtotals for the B column – caseroR
I get the same issue, the closest workaround is to get A without subtotals as outline form, not compact, B with subtotals as outline form, not compact, with top subtotals. However, this will put B one line below A. – nutsch

1 Answers

2
votes

Since you're not interested in subtotals for the first column (A), I suggest creating a separate column containing the concatenation of A and B, which then yield the desired result:

enter image description here

Unique combinations are grouped and totalled correctly.