Subtotals by month
 

Am trying to do a comprehensive cash book, and would like to transfer
monthly subtotals to a cash flow planner.

Which function is best to subtotal data by month.

TIA
Bruce

Just to clarify, below is a section of the spreadsheet.
What I want to do is create subtotals for each month for the vehicle column.
However, I don't know how to do this with the functions. Specifically how to
select values that correspond to a certain date range.

Any help appreciated.



Date Acct Details cat. Code Amt inc gst gst bus. component Vehicle .80
bus.
01/07/04 MC Dodo Internet internet 23.90 1.88
02/07/04 MC Independ Fuel Supplies Brenndale vehicle 17.05 1.13 12.40
05/07/04 cdia Martin S Iga Sprmkt Samford Au 039523 private use 213.06
08/07/04 cdia Withdrawal 4185 invested 8,000.00
08/07/04 MC Caltex Lawnton vehicle 27.67 1.83 20.12
08/07/04 MC Qld Transport Strathpine vehicle 62.30 4.12 45.31

Closest solution I have so far is to make a separate column for month
=month(a2)

Then use =SUM(IF(B2:B48=10,H2:H48),0)
10 being october

With a separate column, created with:

=MONTH(A2)

in B2:

=SUMIF($B$2:$B$48,K2,$H$2:$H$48)

where K2 is a month number like 10.

But, if you opt for this route, include the year component:

B2:

=A2-DAY(A2)+1

and in K2 enter as the date condition something like:

1-Oct-2005

which is a first day date of the month/year of interest.

Since you have a small range to process, you might want to opt for:

=SUMPRODUCT(--($A$2:$A$48-DAY($A$2:$A$48)+1=K2),$H$2:$H$48)

where K2 houses the first day date of the month/year of interest.

Bruce Gray wrote:
Closest solution I have so far is to make a separate column for month
=month(a2)

Then use =SUM(IF(B2:B48=10,H2:H48),0)
10 being october

Thanks ALadin

I went with your first suggestion, which is similar to what I derived.



With a separate column, created with:

=MONTH(A2)

in B2:

=SUMIF($B$2:$B$48,K2,$H$2:$H$48)

where K2 is a month number like 10.

But, if you opt for this route, include the year component:

B2:

=A2-DAY(A2)+1

and in K2 enter as the date condition something like:

1-Oct-2005

which is a first day date of the month/year of interest.

Since you have a small range to process, you might want to opt for:

=SUMPRODUCT(--($A$2:$A$48-DAY($A$2:$A$48)+1=K2),$H$2:$H$48)

where K2 houses the first day date of the month/year of interest.

Bruce Gray wrote:
Closest solution I have so far is to make a separate column for month
=month(a2)

Then use =SUM(IF(B2:B48=10,H2:H48),0)
10 being october