# Excel Subtotals With Duplicate Grand Totals

Do you use Excel's Subtotal command to automatically create subtotals in a list? I prefer pivot tables, but still use Subtotals in a few macros for clients. Last week, one of them asked why there were duplicate Grand Totals after applying two levels of subtotals.

### Why Are There Two Grand Totals?

That was a good question, and when I created subtotals on different data, there was only one Grand Total, as expected. Google was no help in solving the mystery, so I took a closer look at the two data sets, to see if I could spot a difference.

I had also been asked to check on an error in one of the columns, so I decided to tackle that problem first – it seemed easier!

### No Error = No Duplicate Grand Totals

The error was in one of the columns that was being subtotaled, and carried down to the grand totals. Fortunately, the formula problem was easy to solve – it was caused when a formula tried to subtract a cell that contained text.

Then a miracle happened – when I ran the subtotal command again, there were no duplicate grand totals! So, it seemed that the error was causing the duplicates.

To test my theory, I created an error in my test data. Next, I applied subtotals, and subtotaled the column with the error. Woohoo! The table had 2 grand totals, instead of one.

### Prevent Duplicate Grand Totals

After that exhausting round of testing (3 or 4 tests), I'm convinced that those mysterious duplicate grand totals are caused by errors in any column that is being subtotaled.

• To prevent duplicate grand totals, fix those errors, if possible, or use IFERROR or IF and ISERROR to override error results.
• If you can't change the formulas, just hide the rows with duplicate grand totals, or ignore them. (Or tell your client, "It's not a bug, it's a feature.")

### Video: Subtotals With Duplicate Grand Totals

To see how to apply two subtotals to a list, and see the effect that errors have, watch this short video tutorial. The timeline is listed below the video.

0:00 Introduction
0:31 Sorted List
0:51 First Subtotal
1:44 Second Subtotal
2:18 Remove Subtotals
2:27 Duplicate Grand Totals
2:53 Apply Subtotals
3:24 Prevent Duplicates
4:29 Get the Sample File