# How to evenly spread out and sum cost items across multiple months based on criteria?

#### ETH20k

##### New Member
Element 1: Dynamic Table
• The table has a "start", "end", "cost" & "category" column with multiple row entries.
• e.g. From "Apr.22" until "Jun.22", I have a TOTAL of "3'000 USD" as "marketing cost".
Element 2: Cash Flow Model
• I want to feed the table data into a cash flow model, based on criteria of category, date and amount
• The Cash Flow (CF) Model shows the monthly CF, whereas each column holds one month and first column shows the cashflow category, e.g. marketing, etc.
Question:
How do I aggregate the sum of all table rows and equally allocate the monthly cost to the right month and criteria of the CF Model.
• My difficulty is to divide and allocate the total amount across the relevant months in an aggregate.
Thank you.
RedditCFS.xlsx

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
A26, there was a space after "Production labor " !!!
RedditCFS.xlsx
ABCDEFGH
1
2COSTS
3DateEndSubcategoryCost USDmonthly cost
4apr/22apr/22Raw Materials\$ -74.000,00-\$74.000,00
5apr/22apr/22Raw Materials\$ -15.000,00-\$15.000,00
6apr/22mei/22Payroll\$ -18.000,00-\$9.000,00
7apr/22mei/22Payroll\$ -14.000,00-\$7.000,00
8apr/22mei/22Payroll\$ -12.000,00-\$6.000,00
9apr/22apr/22Production Labor\$ -30.000,00-\$30.000,00
10apr/22apr/22IT Development\$ -7.000,00-\$7.000,00
11apr/22apr/22IT Development\$ -20.000,00-\$20.000,00
12apr/22apr/22IT Development\$ -5.000,00-\$5.000,00
13apr/22jun/22Marketing\$ -55.000,00-\$18.333,33
14apr/22apr/22Marketing\$ -35.000,00-\$35.000,00
15mei/22jun/22Marketing\$ -70.000,00-\$35.000,00
16mei/22jun/22Marketing\$ -20.000,00-\$10.000,00\$ -375.000,00
17\$ -375.000,00
18
19
20CASH FLOW
21apr/22mei/22jun/22jul/22aug/22
22
23Revenue\$ 100.000,00\$ 200.000,00\$ 300.000,00\$ 400.000,00\$ 500.000,00
24COGS\$ 119.000,00\$ -\$ -\$ -\$ -
25Raw Materials\$ -89.000,00\$ -\$ -\$ -\$ -\$ -89.000,00
26Production Labor\$ -30.000,00\$ -\$ -\$ -\$ -\$ -30.000,00
27Gross Profit\$ -19.000,00\$ 200.000,00\$ 300.000,00\$ 400.000,00\$ 500.000,00
28Gross Margin-19%100%100%100%100%
29Expenses\$ -107.333,33\$ -85.333,33\$ -63.333,33\$ -\$ -
30IT Development\$ -32.000,00\$ -\$ -\$ -\$ -\$ -32.000,00
31Marketing\$ -53.333,33\$ -63.333,33\$ -63.333,33\$ -\$ -\$ -180.000,00
32Payroll\$ -22.000,00\$ -22.000,00\$ -\$ -\$ -\$ -44.000,00
33Utility & other\$ -\$ -\$ -\$ -\$ -\$ -
34Operating Profit (EBIT)\$ -126.333,33\$ 114.666,67\$ 236.666,67\$ 400.000,00\$ 500.000,00
35Operating Profit Margin -126%57%79%100%100%
36
Sheet1
Cell Formulas
RangeFormula
F4:F16F4=+[@[Cost USD]]/(DATEDIF([@Date],[@End],"m")+1)
H16H16=SUM(Table2[Cost USD])
H17H17=SUM(H25:H33)
B24:F24B24=-SUM(B25:B26)
B25:F26,B30:F33B25=SUMPRODUCT(Table2[[monthly cost]:[monthly cost]]*(Table2[[Date]:[Date]]<=B\$21)*(B\$21<=Table2[[End]:[End]])*(Table2[[Subcategory]:[Subcategory]]=\$A25))
B27:F27B27=B23-B24
B28:F28B28=IF(B27<>0,(B27/B23),0)
B29:F29B29=SUM(B30:B33)
B34:F34B34=B27+B29
B35:F35B35=IF(B34<>0,(B34/B23),0)
H25:H26,H30:H33H25=SUM(B25:F25)

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Cross posted at: How to evenly spread out and sum cost items across multiple months based on criteria?
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

@BSALV ... this is phenomenal!!! First time I ever posted something and am amazed by the help. Thank you

you're welcome.

Replies
3
Views
241
Replies
3
Views
252
Replies
6
Views
555
Replies
2
Views
381
Replies
9
Views
611

1,219,917
Messages
6,150,946
Members
450,996
Latest member
darko1515s

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back