I have a spreadsheet of data, about 7k lines that I need to cost allocate. I've added a small portion below as an example. What I would like to display with a formula is the total for each cost center by document number. In other words, cost center "2539" below would display Doc# 40859728 as $5.00 and Doc# 40885398 as $5.65.
I would also like to further allocate the total for Doc# 40874160. Although one entry, for $3.81, solely belongs to cost center "2616", an additional entry for that Doc# belongs to more than one cost center, "2616" and "3571". How can I display that split? I would like Doc# 40874160 to return two results: a total of $4.02 for cost center "2616" and only $0.21 for "3571".
I have tried a pivot table, but I'm not as experienced with those and not sure if that would even help. I'm ok with formulas, but not so well with nested functions so I've not been able to figure out the best way to display this.
Thanks for any help you can provide!!
| Document | Amount | Cost Center |
| 40886025 | $ 5.53 | 2529 |
| 40861530 | $ 7.70 | 3563 |
| 40881086 | $ 7.70 | 3561 |
| 40861530 | $ 9.90 | 3562 |
| 40881087 | $ 7.70 | 3564 |
| 40859728 | $ 5.00 | 2539 |
| 40885398 | $ 2.30 | 2539 |
| 40885398 | $ - | 2539 |
| 40885398 | $ 3.35 | 2539 |
| 40862560 | $ 1.31 | 2648 |
| 40862560 | $ 1.62 | 2648 |
| 40862560 | $ 1.24 | 2648 |
| 40862560 | $ 1.31 | 2616 |
| 40874160 | $ 3.81 | 2616 |
| 40874160 | $ 0.42 | 2616/3571 |
| 40857867 | $ 5.40 | 2551/3560 |
| 40857867 | $ 2.44 | 2551 |
| 40857867 | $ 9.56 | 2551 |
| 40881088 | $ 5.00 | 3560/3566/3575/3579 |
| 40885934 | $ - | 2631 |
| 40885934 | $ - | 2631 |
| 40863091 | $ 5.30 | 2615 |
| 40863091 | $ 5.46 | 2615 |
| 40863091 | $ 5.83 | 2615 |
| | $ 97.88 | |
