With dates in column A, description in column B, income in C and Expenditure in D on the P&L worksheet:
On the summary worksheet:
Cell A2 "Description"
Cell B2 "Income"
Cell C2 "Expense"
Cell D1 "Start date"
Cell D2 "End date"
Then select cells E1 to F2 and format them in your preferred date format
In cell E1 enter the first date for your data, say 01-Jan-2010
In cell E2 enter this formula: =EMONTH(E1,0) and E2 will display the last day of the month of the date above it
[see note at end of this post about EOMONTH if you are using Excel 2003 or earlier]
In cell F1 enter = E2+1 which will display the first day of the next month
In cell F2 enter =EMONTH(F1,0)
Now select F1 and F2 and drag them to extend the formulas to column P
P1 will show 01 December and P2 31 December.
The formulas will handle leap years as well.
In column A enter the various descriptions.
Use columns B and C as before for totals of each income/expense item if required.
In cell E3 enter this formula: =SUMPRODUCT(('P&L'!$B$2:$B$150=$A3)*('P&L'!$A$2:$A$150>=E$1)*('P&L'!$A$2:$A$150<=E$2)*('P&L'!$D$2:$D$150))
Note the position of the $ signs - they are crucial to extending this formula.
The last row with data is row 150 on the P&L worksheet. Change this to include the maximum rows you are likely to use. Including empty cells will not affect the results, and save you having to change the range of used cells as new data is added.
Drag the formula down to extend it for as many rows as there are descriptions in column B.
Now edit the formulas on Income rows to: =SUMPRODUCT(('P&L'!$B$2:$B$150=$A3)*('P&L'!$A$2:$A$150>=E$1)*('P&L'!$A$2:$A$150<=E$2)*('P&L'!$C$2:$C$150))
Now select all the cells with formulas in column E, starting at E3
and drag them across to column P
You should now have income and expenditure for each item for each date period.
(To make data range management easier, you could use named ranges for the data on the P&L sheet.
I selected cells A2 to A150 and named them PLDate
Cells B2 to B150 were named PLDesc
and the two income/expenditure columns were named PLInc and PLExp
The formula in E3 on the Summary worksheet [if this was an income row] becomes:
E4 - an expenditure row - becomes:
The same approach to summing the data for months can be used for quarterly or annual expenditure.
Use the same formulas but put them under different data ranges.
Note about EOMONTH()
The EOMONTH function is standard in Excel 2007 and 2010
In Excel 2003 and earlier, the 'Analysis Toolpak' has to be activated. It is installed, but not active.
In Excel 2003, click on the Tools menu item, then select Add-Ins and from the Add-In dialog box check the check-box next to AnalysisToolpak and click OK. EOMONTH will now be available.
PS I re-read you post and see that you don't want the original totals in columns B & C.
Just follow these instructions and when it's working you can delete columns B & C, and the formulas will adjust appropriately.