Book1.xlsx | |||||
---|---|---|---|---|---|

A | B | C | |||

1 | TOTAL VALUE | ||||

2 | Project | Value | |||

3 | D0001 | 100,000 | |||

4 | D0002 | 110,000 | |||

5 | D0003 | 120,000 | |||

6 | D0004 | 130,000 | |||

7 | E0001 | 140,000 | |||

8 | E0002 | 150,000 | |||

9 | E0003 | 160,000 | |||

10 | E0004 | 170,000 | |||

11 | E0005 | 180,000 | |||

12 | E0006 | 190,000 | |||

13 | |||||

14 | WORK IN PLACE | ||||

15 | Project | Value | |||

16 | D0001 | 1,000 | |||

17 | D0004 | 4,000 | |||

18 | E0001 | 5,000 | |||

19 | E0002 | 6,000 | |||

20 | E0005 | 9,000 | |||

21 | E0006 | 10,000 | |||

22 | |||||

23 | |||||

24 | |||||

25 | D | 595,000 | 235,000 | ||

26 | E | 595,000 | 360,000 | ||

Sheet1 |

Cell Formulas | ||
---|---|---|

Range | Formula | |

B25 | B25 | =SUM(IF((COUNTIF(A3:A21,A3:A21)>1),IF(MATCH(A3:A21,A3:A21,0)<>ROW(A3:A21)-MIN(ROW(A3:A21))+1,B3:B21),B3:B21)) |

C25 | C25 | =B4+B5+B16+B17 |

B26 | B26 | =SUM(IF((COUNTIF(A3:A21,A3:A21)>1),IF(MATCH(A3:A21,A3:A21,0)<>ROW(A3:A21)-MIN(ROW(A3:A21))+1,B3:B21),B3:B21)) |

C26 | C26 | =B9+B10+B18+B19+B20+B21 |