sort by month with a monthly total
 

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.

sort by month with a monthly total
 

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" wrote in message
...
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.

sort by month with a monthly total
 

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" wrote in message
...
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.

sort by month with a monthly total
 

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" wrote in message
...
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" wrote in message
...
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.