.I have a set of data (more rows than the example shows) where by I am looking for a macro that can enter a formulae to sum up ranges of cells dependant on column B entry. In my example below I have a data extract, however the data will not always be in the same order.

I am looking for VBA to enter formulas in the blank cells of column C.

Where the entry in column B is an "SS" I need to sum up all the column B "CC" entries immediately above ( not to include any CC entries already summed.

Then when there is an "S" in column B I need a formulae to sum up ALL the SS values above PLUS ANY CCs which have not been summed to a SS. (see formula in E14 of my data)

Once an "S" is summed then that is the end of that section and I then need to repeat the formula entries in the next section until an S is reached again.

Finally ending up with a formula to sum up all the "S" entries.

I have made entries in Col D and Col E of my data example to try to help with my requirements. Any help gratefully received.

1 | required result | basic Formula | ||

2 | COL B | Col C | Col D | Col E |

3 | cc | 1 | ||

4 | cc | 12 | ||

5 | ss | 13 | =SUM(C3:C4) | |

6 | cc | 3 | ||

7 | cc | 5 | ||

8 | cc | 3 | ||

9 | ss | 11 | =SUM(C6:C8) | |

10 | cc | 2 | ||

11 | ss | 2 | =SUM(C10) | |

12 | cc | 5 | ||

13 | s | 31 | =SUM(D5,D9,D11,C12) | |

14 | cc | 3 | ||

15 | cc | 8 | ||

16 | cc | 9 | ||

17 | cc | 12 | ||

18 | ss | 32 | =SUM(C14:C17) | |

19 | cc | 4 | ||

20 | cc | 63 | ||

21 | ss | 67 | =SUM(C19:C20) | |

22 | s | 99 | =D18+D21 | |

23 | TOTAL | 130 | =D13+D22 |