Excel Move subtotal

[djj55]

Hello, Excel 2002
I would like to move my subtotal to a new column.

When I try the copy it either copies the data between or pastes to consecutive rows. Thus B2, B10, B15 would either copy everything from b2 to b15 or paste to C2, C3, c4.

What I want is when I copy B2, B10 and B15 they paste to C2, C10, C15 (visible cells).

Copy is not the problem paste is.

Hope this makes sense.

Thank you,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[SkipVought]

Hi,

If you are using the Data > Subtotal feature, your choice is either ABOVE or BELOW the group, in the column being aggregated.

You can, however, use a FUNCTION to do your subtotals, rather than the feature described above. If you have only ONE criteria, you can use the SUMIF function. If you have more than one grouping/criteria, you can use the SUMPRODUCT function. In this way, your subtotal can be put in any column you like. Oh, yes, you'll need the contain the function in an IF function to control when the aggregations is displayed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[djj55]

Thank you for your answer. I am really using the Data>Subtotal as a quick way to insert a line after a group. I used count just to give an extra bit of info.

Thanks again,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[Gavona]

Use the Group and Outline symbols on the left to show only subtotals.
Highlight (select) these cells
Edit,GoTo,Special Visible cells only
Insert, Cells Shift to right

(Although if you really only want the blank rows then once you have selected the visible cells just hit the Delete key.)

Gavin

 

[Gavona]

Thinking about it, there is a slightly simpler way.
Before creating your subtotals ensure you have a blank column to the right of your data. From your post this is clearly column C. Add a column heading of (say) "Number of Instances".
Now when you use the Data>Subtotal choose to add the subtotals only to this column.
So now the only issue you have is that the formulae in column C refer to Column C when you would like them to refer to column B.
Highlight column C and use Edit>Replace to fix that. (You will need to ensure that the option to only look at entire cell contents is not checked).



Gavin

 

[djj55]

Gavona, good suggestion with the extra column. I will try that the next time.

Thanks

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!