Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weekly and Monthly Subtotals | Excel Discussion (Misc queries) | |||
Referencing data in different worksheet based on month name | Excel Worksheet Functions | |||
Month Year Date Format | Excel Worksheet Functions | |||
Accounting Month vs. Calendar Month | Excel Worksheet Functions | |||
How to extract month number from month name | Excel Discussion (Misc queries) |