Spreadsheet: 783986d1655324299-aggregate-amount-lookups-cost-reduction2
Sheet: Sales

 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2022-12-31 00:00:00
x
 |  | # | Items |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | I am curious if there is a better way to do this - currently using a sum of sumifs, which makes the formula harder to trace and audit, is there a simpler formula for what I am doing here, which is returning the sale proceeds for each item
 |  | 1
 |  |  |  |  |  |  |  |  |  | Cost - Method 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | I Would like this row of cells to return the sum of the "Current Cost" of all items sold in this period, so for example Item 1 is sold in June 2023 so this cell should return 75 |  |  |  |  |  |  |  |  |  |  |  | This cell would need to return the sum of the Current Cost for each of Items 2 and 3 for example
 |  |  |  |  |  |  |  |  |  | Cost - Method 2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | I would like this row to return the amount of Current Cost proportional to the % of proceeds that period, for example in this period Item 3 recognizes 25% of its proceeds or $50, and the current value is 100, so this cell should return 25. I would like this cell to return the sum of all such a value for all items that are sold in that period
 | x
 | x |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Sale 1 |  |  | Sale 2 |  |  | Sale 3 |  |  | Sale 4 |  |  | Sale 5
 |  | No. | Items |  |  |  | Total Invested | Total Sold Previously | Current Cost | Current Value |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Total Invested |  |  |  |  | Return on Investment | Date | % | Proceeds | Date | % | Proceeds | Date | % | Proceeds | Date | % | Date | Date | % | Proceeds
 | x | 1 | Item 1 |  |  |  | 100 | 25 | 75 | 225 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2 | 2023-06-30 00:00:00 | 1 |  |  | 0 |  |  | 0 |  |  | 0 |  |  | 0
 |  |  | Item 2 |  |  |  | 100 | 0 | 100 | 150 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2 | 2024-06-30 00:00:00 | 1 |  |  | 0 |  |  | 0 |  |  | 0 |  |  | 0
 |  |  | Item 3 |  |  |  | 100 | 0 | 100 | 100.001 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2 | 2025-06-30 00:00:00 | 0.25 |  |  | 0.25 |  |  | 0.25 |  |  | 0.25 |  |  | 0
 |  |  | Item 4 |  |  |  | 100 | 0 | 100 | 100.0001 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2 | 2025-09-30 00:00:00 | 0 |  | 2026-06-30 00:00:00 | 1 |  |  | 0 |  |  | 0 |  |  | 0
 |  |  | Item 5 |  |  |  | 100 | 0 | 100 | 100.00001 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2 | 2043-03-31 00:00:00 | 1 |  |  | 0 |  |  | 0 |  |  | 0 |  |  | 0