I have a template budget which has a list of activities and the associated
time (Hours) and Costs (GBP) in the column next to them, while in the first
column is the task code (see below).

Task code Task Cost (GBP) Hours
1 Task 1 1000 10
2 Task 2 2000 20
3 Task 3 3000 30
4 Task 4 4000 40
5 Task 5 5000 50
6 Task 6 6000 60
7 Task 7 7000 70

We then map these codes to several milestones in order to build up an idea
of how much effort and spend is needed to achieve these milestones (such as
in the table below).

Mapped task codes Deliverable Cost (GBP) Hours
1,2,7 Deliverable 1 10000 100
3,4 Deliverable 2 7000 70
5 Deliverable 3 5000 50
6 Deliverable 4 6000 60

What I want to know is how to create a formula to go in the Cost and Hours
column of the second table that will automatically calculate the Hours and
Cost based upon the numbers entered in the first column of the second table
(e.g. calculate the 10000 based upon 1,2,7 being entered in the first column)
- anyone got any ideas?

For those of you interested I have managed to get an answer to this.

Create a user defined function to split the values using the code below

Function SplitIt(rngIn As Range) As Variant
SplitIt = Split(rngIn.Value, ",")
End Function

and then use:
=SUMPRODUCT(SUMIF(LIST OF TASKS,splitit(TASK MAPPING) ,COST))

e.g. =SUMPRODUCT(SUMIF(A2:A8,splitit(A10),C2:C8))

"jellyroller" wrote:

I have a template budget which has a list of activities and the associated
time (Hours) and Costs (GBP) in the column next to them, while in the first
column is the task code (see below).

Task code Task Cost (GBP) Hours
1 Task 1 1000 10
2 Task 2 2000 20
3 Task 3 3000 30
4 Task 4 4000 40
5 Task 5 5000 50
6 Task 6 6000 60
7 Task 7 7000 70

We then map these codes to several milestones in order to build up an idea
of how much effort and spend is needed to achieve these milestones (such as
in the table below).

Mapped task codes Deliverable Cost (GBP) Hours
1,2,7 Deliverable 1 10000 100
3,4 Deliverable 2 7000 70
5 Deliverable 3 5000 50
6 Deliverable 4 6000 60

What I want to know is how to create a formula to go in the Cost and Hours
column of the second table that will automatically calculate the Hours and
Cost based upon the numbers entered in the first column of the second table
(e.g. calculate the 10000 based upon 1,2,7 being entered in the first column)
- anyone got any ideas?