# subtotals appear in the wrong place after running macro

#### shayb

##### Board Regular
hi everyone.

I hope i can explain this well to you all. I start by recording a macro. To get the Monthly Order Reduction Opportunity, first I have to get the Groups Average Order Size (Groups Total Sales divided by Groups Total Order Count). Then I come up with the target number of orders they should be placing, based on Groups Avg Order Size (Total Sales divided by Groups Avg Order Size). To get the Monthly Order Reduction, I take the Target Order Count minus the Actual Order Count and divide that by 6 (for 6 months).

First I take out all of the positive numbers (everything 0 and greater) because that means they're right on target or exceeding their target. Now I have the negative numbers left.

I ctrl+F all the '-' and replace them with blanks, so now I have whole numbers. Then I make a copy of this spreadsheet and move it over into the same book.

After that I remove the subtotals for the other columns and then do a sum just on the Monthly Order Reduction Column. Then I just copy that column, with the subtotals, to the original spreadsheet and it lines up. The problem is when I stop the macro and run it again, the subtotals are not right and are placed a few spaces under where they should be. Here is an example. I don't know where the '38' comes from nor the '0's'. '46' is supposed to be in the NY Total and 50 in the Grand Total.

Can anyone help?

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
anyone have any idea as to what i can search for on the board?

i've already used 'subtotal' and 'subtotals' and still can't find anything.

As i understand from the exhibit, you already have separate columns for the various stages of the calculations. Now you want the sum of only the negative numbers for each group in column J. If this is correct, there are two ways of achieving this:

1. In the formula used to derive Column J, change the structure of the formula to bring up only negative numbers. If you are not able to do this, post your existing formula for suggestions.

2. If you need to show all values (positive, negative, zero) in Column J but have the sum of the negative values only, use a suitable sumif formula e.g., SUMIF(A1:A100,"<0",A1:A100)

Replies
4
Views
88
Replies
4
Views
138
Replies
4
Views
267
Replies
1
Views
528
Replies
11
Views
433

1,219,807
Messages
6,150,350
Members
450,952
Latest member
Zung

### 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?

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