sort by month with a monthly total

[sdg8481]

Hi,

What i'm wanting to do is to set a spreadsheet that sorts a range of rows by
date order, but then at the end of each month have a total line under the
last entry for that month before going straight on the first entry of the
next month.

I can use a command button to sort the rows, but how do you keep the totals
rows in the same position (after each month). and i must be able to add new
rows for previous months aswell

Hope you can help,

Thanks in adavnce.

[Earl Kiosterud]

sdg,

You can use Data - Subtotals, with a total on a change in the month column.
You should remove the subtotals before sorting the table again, then reapply
subtotals.

If you don't have a month column, you can use
=month(A2)

Another way, if you don't want the month totals physically under each group,
but in a separate table, is to use a formula for each month's totals. If
the dates are in column A, the formula above is in column B, and the amounts
to be summed are in column C:

=SUMIF(A2:A65536,1,C2:C65536) for Jan
=SUMIF(A2:A65536,2,C2:C65536) for Feb
etc

Earl Kiosterud
www.smokeylake.com


"sdg8481" <sdg8481@discussions.microsoft.com> wrote in message
news:6D2567A9-854B-47BC-9EE5-CA5422907D86@microsoft.com...
> Hi,
>
> What i'm wanting to do is to set a spreadsheet that sorts a range of rows
> by
> date order, but then at the end of each month have a total line under the
> last entry for that month before going straight on the first entry of the
> next month.
>
> I can use a command button to sort the rows, but how do you keep the
> totals
> rows in the same position (after each month). and i must be able to add
> new
> rows for previous months aswell
>
> Hope you can help,
>
> Thanks in adavnce.

[sdg8481]

Thanks but i do want a monthly row to be under the monthly data, even when
sorted will this do that?

"Earl Kiosterud" wrote:

> sdg,
>
> You can use Data - Subtotals, with a total on a change in the month column.
> You should remove the subtotals before sorting the table again, then reapply
> subtotals.
>
> If you don't have a month column, you can use
> =month(A2)
>
> Another way, if you don't want the month totals physically under each group,
> but in a separate table, is to use a formula for each month's totals. If
> the dates are in column A, the formula above is in column B, and the amounts
> to be summed are in column C:
>
> =SUMIF(A2:A65536,1,C2:C65536) for Jan
> =SUMIF(A2:A65536,2,C2:C65536) for Feb
> etc
>
> Earl Kiosterud
> www.smokeylake.com
>
>
> "sdg8481" <sdg8481@discussions.microsoft.com> wrote in message
> news:6D2567A9-854B-47BC-9EE5-CA5422907D86@microsoft.com...
> > Hi,
> >
> > What i'm wanting to do is to set a spreadsheet that sorts a range of rows
> > by
> > date order, but then at the end of each month have a total line under the
> > last entry for that month before going straight on the first entry of the
> > next month.
> >
> > I can use a command button to sort the rows, but how do you keep the
> > totals
> > rows in the same position (after each month). and i must be able to add
> > new
> > rows for previous months aswell
> >
> > Hope you can help,
> >
> > Thanks in adavnce.
>
>
>

[Earl Kiosterud]

Yes first sort the table on your date column you'll need the month formula I
gave you in a column somewhere in Data - Subtotals use that column in "for
each change in"

"sdg8481" <sdg8481@discussions.microsoft.com> wrote in message
news:08A224B1-504B-41E2-8343-0A1D2EB4202A@microsoft.com...
> Thanks but i do want a monthly row to be under the monthly data, even when
> sorted will this do that?
>
> "Earl Kiosterud" wrote:
>
>> sdg,
>>
>> You can use Data - Subtotals, with a total on a change in the month
>> column.
>> You should remove the subtotals before sorting the table again, then
>> reapply
>> subtotals.
>>
>> If you don't have a month column, you can use
>> =month(A2)
>>
>> Another way, if you don't want the month totals physically under each
>> group,
>> but in a separate table, is to use a formula for each month's totals. If
>> the dates are in column A, the formula above is in column B, and the
>> amounts
>> to be summed are in column C:
>>
>> =SUMIF(A2:A65536,1,C2:C65536) for Jan
>> =SUMIF(A2:A65536,2,C2:C65536) for Feb
>> etc
>>
>> Earl Kiosterud
>> www.smokeylake.com
>>
>>
>> "sdg8481" <sdg8481@discussions.microsoft.com> wrote in message
>> news:6D2567A9-854B-47BC-9EE5-CA5422907D86@microsoft.com...
>> > Hi,
>> >
>> > What i'm wanting to do is to set a spreadsheet that sorts a range of
>> > rows
>> > by
>> > date order, but then at the end of each month have a total line under
>> > the
>> > last entry for that month before going straight on the first entry of
>> > the
>> > next month.
>> >
>> > I can use a command button to sort the rows, but how do you keep the
>> > totals
>> > rows in the same position (after each month). and i must be able to add
>> > new
>> > rows for previous months aswell
>> >
>> > Hope you can help,
>> >
>> > Thanks in adavnce.
>>
>>
>>