Summarise excel cashbook by month

Summarise excel cashbook by month

Didn't find your answer?

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.

avatar
By Richard Willis
21st May 2015 13:00

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!)

 

 

Thanks (1)
Replying to Wanderer:
avatar
By cameron000
21st May 2015 13:48

Thanks a lot!

You've been a great help.

Just a small thing, your formula should read  =SUMIF($A$2:$A$#,$A?,C$2:C$#)   (using colons instead of dashes) as I'm sure you understand.

Thanks again!

 

Cameron

Thanks (0)
Replying to Accountant A:
avatar
By Richard Willis
21st May 2015 14:00

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!

Thanks (0)
avatar
By Richard Willis
21st May 2015 14:11

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.

Thanks (0)
By Onion4Sage
22nd May 2015 09:32

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? 

Thanks (0)
avatar
By edhy
23rd May 2015 09:09

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

 

Thanks (0)