CODEWITHSUNDEEP
excel-formula
Sam Amedeo
Sam Amedeo

Reputation: 31

A generic formula to sum the values in various sections of an outline

I am putting together a Work Breakdown Structure for projects that my company does on a routine basis. I've organized things into a hierarchical structure that sets the entire project on the highest order with 3 sub orders to break the project up into tasks, sub-tasks, and break down structures, descending in order respectively. The outline looks something like this:

(please ignore the spaces in this numbers here 1.1 is not playing well with the formatting)

To keep it short, I'm looking to change the way we approach our projects by implementing this outline as a template for projects going forward. We use home-brewed project management software that isn't capable of what I'm looking to do so I'm throwing together a prototype Work Breakdown Structure (WBS) in Excel to illustrate my ideas to management. One element that everyone would like to see in the WBS is an estimated duration (which we would then use to calculate estimated completion dates) for the whole project, each task, and each sub-task. These estimated durations should be calculated from the estimated duration of each breakdown structure and propagate up through the hierarchy to provide summations at each level. In other words, each task breakdown structure will be given an estimated time to completion from which we will calculate the total time estimated time to completion for each sub-task,task and project.

Here is what this would look like on a spreadsheet (? is a single character wildcard):

╔════╦═════════╦═════════════════════════════════════════╦═══════════════════════════════════════════════════════════════════╗
║    ║    A    ║                    B                    ║                                 C                                 ║
╠════╬═════════╬═════════════════════════════════════════╬═══════════════════════════════════════════════════════════════════╣
║  1 ║         ║ Component Name                          ║ Estimated Component Duration (days)                               ║
║  2 ║ 1       ║ Whole Project                           ║ Sum of all Estimated Component Durations where column a = 1.?     ║
║  3 ║ 1.1     ║ Task 1                                  ║ Sum of all Estimated Component Durations where column a = 1.1.?   ║
║  4 ║ 1.1.1   ║ Task 1 Sub-task 1                       ║ Sum of all Estimated Component Durations where column a = 1.1.1.? ║
║  5 ║ 1.1.1.1 ║ Task 1 Sub-task 1 breakdown structure 1 ║ 5                                                                 ║
║  6 ║ 1.1.1.2 ║ Task 1 Sub-task 1 breakdown structure 2 ║ 3                                                                 ║
║  7 ║ 1.1.1.3 ║ Task 1 Sub-task 1 breakdown structure 3 ║ 2                                                                 ║
║  8 ║ 1.1.2   ║ Task 1 Sub-task 2                       ║ Sum of all Estimated Component Durations where column a = 1.1.2.? ║
║  9 ║ 1.1.2.1 ║ Task 1 Sub-task 2 breakdown structure 1 ║ 4                                                                 ║
║ 10 ║ 1.1.2.2 ║ Task 1 Sub-task 2 breakdown structure 2 ║ 2                                                                 ║
║ 11 ║ 1.2     ║ Task 2                                  ║ Sum of all Estimated Component Durations where column a = 1.2.?   ║
║ 12 ║ 1.2.1   ║ Task 2 Sub-task 1                       ║ Sum of all Estimated Component Durations where column a = 1.2.1.? ║
║ 13 ║ 1.2.1.1 ║ Task 2 Sub-task 1 breakdown structure 1 ║ 10                                                                ║
║ 14 ║ 1.2.1.2 ║ Task 2 Sub-task 1 breakdown structure 2 ║ 5                                                                 ║
║ 15 ║ 1.2.2   ║ Task 2 Sub-task 2                       ║ Sum of all Estimated Component Durations where column a = 1.2.2.? ║
║ 16 ║ 1.2.2.1 ║ Task 2 Sub-task 2 breakdown structure 1 ║ 10                                                                ║
╚════╩═════════╩═════════════════════════════════════════╩═══════════════════════════════════════════════════════════════════╝

Which when up and running should look like this:

╔════╦═════════╦═════════════════════════════════════════╦═════════════════════════════════════╗
║    ║    A    ║                    B                    ║                  C                  ║
╠════╬═════════╬═════════════════════════════════════════╬═════════════════════════════════════╣
║  1 ║         ║                                         ║                                     ║
║  2 ║         ║ Component Name                          ║ Estimated Component Duration (days) ║
║  3 ║ 1       ║ Whole Project                           ║ 41                                  ║
║  4 ║ 1.1     ║ Task 1                                  ║ 16                                  ║
║  5 ║ 1.1.1   ║ Task 1 Sub-task 1                       ║ 10                                  ║
║  6 ║ 1.1.1.1 ║ Task 1 Sub-task 1 breakdown structure 1 ║ 5                                   ║
║  7 ║ 1.1.1.2 ║ Task 1 Sub-task 1 breakdown structure 2 ║ 3                                   ║
║  8 ║ 1.1.1.3 ║ Task 1 Sub-task 1 breakdown structure 3 ║ 2                                   ║
║  9 ║ 1.1.2   ║ Task 1 Sub-task 2                       ║ 6                                   ║
║ 10 ║ 1.1.2.1 ║ Task 1 Sub-task 2 breakdown structure 1 ║ 4                                   ║
║ 11 ║ 1.1.2.2 ║ Task 1 Sub-task 2 breakdown structure 2 ║ 2                                   ║
║ 12 ║ 1.2     ║ Task 2                                  ║ 25                                  ║
║ 13 ║ 1.2.1   ║ Task 2 Sub-task 1                       ║ 15                                  ║
║ 14 ║ 1.2.1.1 ║ Task 2 Sub-task 1 breakdown structure 1 ║ 10                                  ║
║ 15 ║ 1.2.1.2 ║ Task 2 Sub-task 1 breakdown structure 2 ║ 5                                   ║
║ 16 ║ 1.2.2   ║ Task 2 Sub-task 2                       ║ 10                                  ║
║ 17 ║ 1.2.2.1 ║ Task 2 Sub-task 2 breakdown structure 1 ║ 10                                  ║
╚════╩═════════╩═════════════════════════════════════════╩═════════════════════════════════════╝

I can calculate the component duration values by using writing SUMIFS equations for multiple criteria e.g. cell C3 =SUM(SUMIFS(C:C,A:A,{"1.1","1.2"})) and cell C4 = =SUM(SUMIFS(C:C,A:A,{"1.1.1.*"})). However, this WBS is very much a work in progress so I'll be adding and subtracting rows on a regular basis. Additionally, I will need to write a unique equation for each project task and subtask. My question is: is there a way to write a single equation that can identify it's place in the heirarchy and calculate a the estimated component duration based on that unit's sub components.

In other words cells C2, C3, C4, C8, C11, C12, and C15 (I think I got them all) are all calculated with different equations, is there a single equation I can plug into each cell that will give me the results as presented in my second table?

Thanks for your help!

Upvotes: 0

Views: 369

Answers (1)

Forward Ed
Forward Ed

Reputation: 9894

place individual task durations in column C. Place the following formula in Column D

=SUMPRODUCT((LEFT($A$2:$A$17,LEN($A2))=$A2)*$C$2:$C$17)

because the formula references column C where the individual tasks values are, the formula its self cannot be in column C as that would create a circular reference. Adjust your range reference to suit your data.

POC

Upvotes: 1

Related Questions