MS Excel SUBTOTAL rows are in the wrong order.

Rick Winford 0

I'm using MS 365 Excel desktop app, the latest version. I have duplicated the problem with a small spreadsheet. I have 4 rows of data; each row has Month (Col A) and Country (Col B), followed by 5 columns of stats. The rows are sorted by Month and then by Country. I want to create SUBTOTAL rows first for the Month and then for the Country. If I have just 4 columns of data the SUBTOTAL command works correctly and shows the subtotals in the correct order. If I have 5 columns of data, then the last SUBTOTAL rows are not in the correct position: the order should be Country subtotal, then Month Subtotal row, then the Grant Total. But, with 5 columns of stats, the rows are this order: Month Subtotal, Last Country subtotal, then Grand Total. I tried to include images of the formatting problem, but it appears MS doesn't like that and deleted my question immediately. So, I've rewritten the question with no pictures.

Thanks for your help,

Rick

0 comments

6 answers

Rick Winford 0 Reputation points

2026-02-17T05:15:33.8966667+00:00

Hi Barry, thank you for your response, but I did not manually insert any row. I have gone through the steps multiple times with the same outcome and I have never moved or inserted any row. When I first found this problem it was on a bigger spreadsheet where I use a VBA macro that I wrote to automate this process. I spent several hours recreating the problem with a very small dataset. I find it interesting that it works correctly with 4 columns of data for each month but then breaks with 5.
Please sign in to rate this answer.
Barry Schwarz 5,431 Reputation points

2026-02-17T23:22:08.24+00:00

Can you show us the macro? If nothing else, copy it to the small spreadsheet and upload it again and give us the new link.

Rick Winford 0 Reputation points

2026-02-18T05:50:54.98+00:00

Hi Barry, thank you for your help. here is a scaled down version of the full macro. The problem still shows with this macro. I put it on my drive at https://docs.google.com/spreadsheets/d/1hXJAR1uwAKh5dMSZbTPLmfSCMxoSTSl9/edit?usp=drivesdk&ouid=102375875731166636614&rtpof=true&sd=true

Rick Winford 0 Reputation points

2026-02-18T05:55:17.0766667+00:00

Here’s the macro code as well

Sub setSubTotals() ' ' setSubTotals Macro ' lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row Debug.Print "lastRow #1 = "; lastRow Range("A1:G" & lastRow).Select Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5, 6), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row Debug.Print "lastRow #2 = "; lastRow Range("A1:G" & lastRow).Select Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3, 4, 5, 6), _ Replace:=False, PageBreaks:=False, SummaryBelowData:=True End Sub

Barry Schwarz 5,431 Reputation points

2026-02-18T08:43:03.9+00:00

It looks like you have found a bug in the excel code that is common to the Subtotal tool and the Subtotal method. You could prevent the first country from ever having less than three entries by putting two rows of zeros in front of the first row.

Oddly enough, the problem seems to disappear if the there is another month in the data.

Alternatively, you could replace the current macro with one that "manually" does what the Subtotal method should have done. Scan down the month column and every time it changes insert a row and populate it with the appropriate formulas. Then do the same for the country column. Finally add a Grand Total row at the end of the data.

Rick Winford 0 Reputation points

2026-02-18T13:06:50.14+00:00

Hi Barry, thanks again for your effort on this problem. I was hoping it wasn't a bug and that I was just doing something wrong. I've been using Excel for years and there have been several times when I knew I had found a bug, only to discover what I was doing wrong.

For this problem, as the dataset grows over time (new data every month) I believe I won't see it very often. I have thousands of rows of data that I have automated quite a bit with multiple Power Query flows and VBA scripts. It appears to be such a corner case (two rows of data in the first group of the second subtotal group) that I will probably just work around it by hand. I thought of writing my own subtotal macro, but maybe I will just wait until MS fixes the bug, or AI replaces me in my job.

Thanks again.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

MS Excel SUBTOTAL rows are in the wrong order.

6 answers

Your answer