I am looking to generate a table from a much larger dataset where it sums up planned amounts vs actual amounts by month. Below is a notional idea of the date table, and the table I want the results formated. In the read data have the dataset within a named dynamic range that grows as data is added. Looking to do this with formulas only, no VBA.
Book2 | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | |||
1 | This is a named range (MyRange) | ||||||||||
2 | Project | Funded Amount | Planned | Actual | Month | Planned | Actual | ||||
3 | Apples | 500 | 5/6/2023 | 4/18/2023 | 1/31/2023 | ||||||
4 | Apples | 400 | 6/6/2023 | 5/25/2023 | 2/28/2023 | ||||||
5 | Grapes | 300 | 7/8/2023 | 6/15/2023 | 3/31/2023 | ||||||
6 | Apples | 50 | 7/9/2023 | 7/8/2023 | 4/30/2023 | ||||||
7 | Oranges | 50 | 8/6/2023 | 9/15/2023 | 5/31/2023 | ||||||
8 | Pinapple | 1000 | 9/15/2023 | 9/10/2023 | 6/30/2023 | ||||||
9 | Apples | 800 | 1/5/2024 | 2/1/2024 | 7/31/2023 | ||||||
10 | Apples | 600 | 1/5/2024 | 2/16/2024 | 8/31/2023 | ||||||
11 | Apples | 400 | 2/1/2024 | 1/25/2024 | 9/30/2023 | ||||||
12 | Grapes | 700 | 3/1/2024 | 3/1/2024 | 10/31/2023 | ||||||
13 | Oranges | 500 | 6/1/2024 | 5/1/2024 | 11/30/2023 | ||||||
14 | Pinapple | 900 | 6/1/2024 | 5/28/2024 | 12/31/2023 | ||||||
15 | 1/31/2024 | ||||||||||
16 | 2/29/2024 | ||||||||||
17 | 3/31/2024 | ||||||||||
18 | 4/30/2024 | ||||||||||
19 | 5/31/2024 | ||||||||||
20 | 6/30/2024 | ||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H4:H20 | H4 | =EOMONTH(H3,1) |