Hi,
Hopefully this is an easy one!
I have a full years cashbook on excel with multiple columns. I want to summarise the cashbook on a monthly basis.
I think a pivot table is the correct tool. I know how to group dates into months, my problem is that a pivot table only seems to work with one column of numbers.
I suspect there is a solution to the pivot table problem : put the net values in a column and then put the column headings next to them. But i can't do this quickly enough to make it worthwhile (if someone could explain how to do this using a VLOOKUP or the IF function or INDEX function?)
Any help would be appreciated, I come across this problem time and time again.
I can't manage to ask google the question in the correct way to give me a useful answer!
Thanks,
Cameron
Replies (6)
Please login or register to join the discussion.
One solution (others are available)
Add a column (say A) next to the date column (now B) and insert the formula =month(b2) (assuming first date is in row 2). Copy down.
Create a grid in the same style as your cash book (i.e. same columns) with 1 - 12 in col A. This can be under the cash book if you choose, or on another sheet.
Assuming the former, In Jan col C of your summary type =SUMIF($A$2-$A$#,$A?,C$2-C$#) where # is the last row of data and ? is the current row number. Copy down and across.
Add a total row at the bottom. Add a check row below that and take the summary totals from the cash book totals (S/Be Nil!)
Sorry, yes, of course!
I did a test by sweeping over the ranges, not typing them in.
ED. I vaguely think that may have been a Freudian slip back to Lotus 123!
Another thing
If your cash book transcends a year end I make the 'old' year month formulae =month(b2)+# where # is a number of your choice (? 12 or 100), numbering the summary rows accordingly. 100 is good as you still get 101, 102, etc. If it goes back even further you can add 200,300 etc.
What do you mean?
...my problem is that a pivot table only seems to work with one column of numbers.
PivotTables can work with a many columns of numbers as you like. What are you wanting to do that you think a PivotTable won't do for you?
Sub Totals
Pivot table will work, however people not used to pivot tables, =SUMIFS(), as described by Richards offers more sense of control.
There is one more suggestion. Extract the month in a column by =MONTH(ref) and just run Data > Subtotal and sub total on Change in month. Collapse the outline, you will have subtotals only, expand and see details.
Zubair Edhy