7

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.

caseroR
  • 71
  • 1
  • 4
  • 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 Sep 25 '12 at 13:34
  • 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 Sep 25 '12 at 13:40
  • How do you use the SUBTOTAL function? like =SUBTOTAL(____?) – venkatKA Sep 25 '12 at 13:42
  • I am letting the pivot-table to do subtotals for the B column – caseroR Sep 25 '12 at 13:44
  • I would recommend posting a screenshot of your settings on your pivot table. You can be reasonably sure that the pivot table is not giving you the "wrong" result. You probably have some weird setting options going on. – Stepan1010 Sep 25 '12 at 18:29
  • 3
    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 Sep 25 '12 at 20:55
  • I don't get the problem with the data above. Does the problem persist when you use "Classic Pivot Table layout"? – BKay Apr 12 '13 at 12:29

1 Answers1

2

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.

Werner
  • 14,324
  • 7
  • 55
  • 77