I'm trying to find the total cost for each unique reference in Sheet 2 and paste it into the corresponding Week cell for that Ref in Sheet 1. Below is what my data is going to look like:

**Sheet 2**

The columns with "..." in them have data but aren't relevant for what I'm trying to do. There are potentially an unlimited number of rows and I would like to find the total Cost for each unique reference in Column B

Book1 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|

A | B | C | D | E | F | G | H | |||

1 | ||||||||||

2 | ||||||||||

3 | Ref | … | … | Cost | Date | CW | … | |||

4 | 001 | £ 1.00 | 02/01/2021 | 1 | ||||||

5 | 001 | £ 2.00 | 07/01/2021 | 2 | ||||||

6 | 003 | £ 3.00 | 12/01/2021 | 3 | ||||||

7 | 005 | £ 4.00 | 12/01/2022 | 3 | ||||||

8 | 003 | £ 5.00 | 13/01/2022 | 3 | ||||||

9 | 002 | £ 6.00 | 05/02/2022 | 6 | ||||||

10 | 003 | £ 7.00 | 06/02/2022 | 6 | ||||||

11 | 004 | £ 8.00 | 06/02/2022 | 6 | ||||||

BOM |

**Sheet 3**

This is what I want the end result to look like. I can do it with a formula but it needs to be in VBA. Can someone help a novice?

Book1 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|

A | B | C | D | E | F | G | H | I | J | |||

1 | ||||||||||||

2 | Month | Jan | Jan | Jan | Jan | Feb | ||||||

3 | Ref | … | … | WK. | 1 | 2 | 3 | 4 | 5 | |||

4 | 001 | 100 | 100 | 0 | 0 | 0 | ||||||

5 | 002 | 0 | 0 | 0 | 0 | 100 | ||||||

6 | 003 | 0 | 0 | 200 | 0 | 100 | ||||||

7 | 004 | 0 | 0 | 100 | 0 | 0 | ||||||

8 | 005 | 0 | 0 | 0 | 0 | 100 | ||||||

Sheet3 |

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

Range | Formula | |

F2:J2 | F2 | =TEXT(DATE(YEAR(NOW()),1,F3*7-2),"mmm") |

F4:J8 | F4 | =SUMIFS(BOM!E4:E11,BOM!B4:B11,Sheet3!B4:B8,BOM!G4:G11,Sheet3!F3:J3) |

Dynamic array formulas. |