Identifying and summing data in a multiple column table

C

csrchick

I am working in Excel 2003.

I have a set of timesheets and WIP accounts with each column headed b
the project reference code and each row in the columns the fee earne
that week for that project.

The summary table is set up with the top row having the list of projec
codes and the first column the months of the year. I need to be able t
look up the project code in the main data table and then sum th
relevant data from relevant column to produce a summary table that ha
project code in the first row and then a monthly summary of fees fo
each project below the code.

I've tried a variety of functions including DSUM, SUMIF and VLOOKUP bu
nothing seems to produce the right answer (mostly they are returning
VALUE error.

Any suggestions as to which function will do this task best?

Thanks for your help
 
D

Domenic

I'm not sure if this is what you want, but here's what I have:

Sheet 1 (Summary)

100 200 300 400
Jan-2004 2600 3100 2650 2850
Feb-2004 2950 2650 2850 3100

*top row consists of project codes
*left column consists of dates on a month by month basis
*Put the following formula in cell B2:
=SUM(IF((MONTH(Sheet2!$A$2:$A$5)=MONTH(Sheet1!$A2))*(YEAR(Sheet2!$A$2:$A$5)=
YEAR(Sheet1!$A2)),Sheet2!B$2:B$5)), to be entered using Ctrl+Shift+Enter
*Copy across and down for the full 12 months


Sheet 2 (Data)

100 200 300 400
1/10/2004 1,200 1,500 1,200 1,250
1/28/2004 1,400 1,600 1,450 1,600
2/2/2004 1,200 1,150 1,250 1,450
2/26/2004 1,750 1,500 1,600 1,650


Hope this helps!
 
R

RagDyeR

Would you be so kind Domenic, to use the same size font that everyone else
in these NGs uses.
I'm sure that I'm not the only one with old eyes, that are straining to read
your posts.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

I'm not sure if this is what you want, but here's what I have:

Sheet 1 (Summary)

100 200 300 400
Jan-2004 2600 3100 2650 2850
Feb-2004 2950 2650 2850 3100

*top row consists of project codes
*left column consists of dates on a month by month basis
*Put the following formula in cell B2:
=SUM(IF((MONTH(Sheet2!$A$2:$A$5)=MONTH(Sheet1!$A2))*(YEAR(Sheet2!$A$2:$A$5)=
YEAR(Sheet1!$A2)),Sheet2!B$2:B$5)), to be entered using Ctrl+Shift+Enter
*Copy across and down for the full 12 months


Sheet 2 (Data)

100 200 300 400
1/10/2004 1,200 1,500 1,200 1,250
1/28/2004 1,400 1,600 1,450 1,600
2/2/2004 1,200 1,150 1,250 1,450
2/26/2004 1,750 1,500 1,600 1,650


Hope this helps!
 
D

Domenic

Would you be so kind Domenic, to use the same size font that everyone else
in these NGs uses.
I'm sure that I'm not the only one with old eyes, that are straining to read
your posts.
--

Regards,

RD
Click to expand...

I hadn't realized it could be a problem for some. Is this better?
 
R

RagDyeR

Yes, thank you.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Would you be so kind Domenic, to use the same size font that everyone else
in these NGs uses.
I'm sure that I'm not the only one with old eyes, that are straining to read
your posts.
--

Regards,

RD
Click to expand...

I hadn't realized it could be a problem for some. Is this better?
 
Top