Data > Subtotal using a SUM function for grand totals instead of SUBTOTAL

scottatah

New Member
Joined
Dec 15, 2009
Messages
17
Hello,

I've got a user whom we recently upgraded from Windows XP to Windows 7 (Office 2010 on both). Since the upgrade, whenever she uses the Data>Subtotal function to calculate the sum of a specific column or at each change in a specific row, the grand-totals do not calculate correctly. Excel is using a SUM(X:X) function instead of the SUBTOTAL(9,X:X) and thus it is including the sub-totals in the grand-total and her grand totals are always twice what they should be.

I had her send me one of the spreadsheets so that I could attempt doing the same thing on my computer and have been unable to reproduce the issue. Mine uses the proper subtotal(9,X:X) function and everything works great.

There's not exactly a lot of options to the using Data>Subtotal, so I'm not sure where to look, but I've ensured she had no unusual excel or com add-ins running. I've tried deleting the below registry keys to get excel back to defaults. I've also tried a repair of office. This issue seems to be limited to her computer, but I cannot for the life of me figure out why. The only thing we haven't tried is a reinstall (simply because it requires our desktop team).
HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel
[FONT=&#23435][/FONT]HKEY_LOCAL_MACHINE\Software\Microsoft\Office\14.0\Excel

Any thoughts or suggestions? Or any info I've left out that you require?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
I think I resolved it. Turns out the user had manually calculated and entered totals at the bottom of the spreadsheet and the subtotal function was including those values in it's grand totals. I simply didn't realize this extra row at the time as being manually entered. I initially thouht it was all from the subtotal function.
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top