subtotals appear in the wrong place after running macro

[shayb]

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?

 

[shayb]

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.

 

[P Sitaram]

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)