Monthly Sum

[bern]

Hi All
Can anybody help

Col A2:A300
Dates ranging from Feb this year to future

Col B2:B300
Job costs

Col C2:C300
Month Number based on dates in A2

Col H
How do I add up monthly figures for Job costs?

Using Excel 2002

Bern

[olasa]

Your example:
A.............B.........C.............D...........E
date1.....sum1...month1...year1....YrMth1
date2.....sum2...month2...year2....YrMth2
...

use
=YEAR(A1)&"-"&MONTH(A1)

Then I would use a PivotTable to sum the values per month and year.
See this picture: http://www.contextures.com/xlPivot02.html

Ola Sandström


Example of how to make a Pivottable:
http://www.excelforum.com/showthread.php?t=351287

[bern]

Thanks
I want to show monthly sums of job costs in Col D (sorry not H)
There could be up 20 entries for 1 month

Col C has a formula =VLOOKUP(A2:A300Months) for each entry

All I need is a monthly total of Job costs

Bern

[olasa]

Well perhaps this can help

D2: =SUMIF(C2:C300,1,B2:B300)
It will sum all amounts in B where the month in col.C is equalt to 1.

Ola

[bern]

Hi
Thanks once again but......
It does not quite work for what I need, perhaps a better explanation of the spreadsheet:

Col A Col B Col C Col D
R1 Date Job cost =VLOOKUP(A2Months)
R2 25/02/05 £200 2
R3 27/02/05 £100 2
R4 01/03/05 £100 3
R5 04/03/05 £150 3

I want to calculate the total job cost each month

bern

[olasa]

Could you specify how you like the end result to look like.
Both suggestions will calculate the total job cost each month

For example suggestion2:
Col E.....Col F...
1...........=SUMIF(C2:C300,E1,B2:B300)
2...........=SUMIF(C2:C300,E2,B2:B300)
3...........=SUMIF(C2:C300,E3,B2:B300)

Ola