Advanced Lookup Sum Equation

[Bkisley]

Hey everyone!

I have one set of data that I need summed up and shown properly on one row in a different set of data

Example Data
Investment cost Depreciation Life Year Depreciation Starts
25,000 3 years 2017
50,000 5 years 2018
75,000 3 years 2019
100,000 5 years 2019

In Cell H4 (2017) I would need (25,000/3)
In Cell I4 (2018) I would need (25,000/3) + (50,000/5)
In Cell J4 (2019) I would need (25,000/3) + (50,000/5) + (75,000/3) +(100,000/5)
In Cell K4 (2020) I would need (50,000/5) + (75,000/3) + (100,000/5)
In Cell L4 (2021) I would need (50,000/5) + (75,000/3) + (100,000/5)
In Cell M4 (2022) I would need (50,000/5) + (100,000/5)
In Cell N4 (2023) I would need (50,000/5) + (100,000/5)
In Cell O4 (2024) I would need that to be 0 since my depreciation is done

So data set one has information about investment cost, depreciation life, and the year depreciation starts
Investment cost = Column C
Depreciation life = Column D -> This can be 3, 5, 10, or 20 years
Year Depreciation Starts = Column E

Data set two has one line item called Depreciation Expense. This is the row where I need my equation to go to aka Cells H4:O4

If something is not clear please let me know!

 

[Eric W]

Maybe:

	C	D	E	F	G	H	I	J	K	L	M	N	O
Investment Cost	Depreciation Life	Year Depreciation Starts

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"] $ 25,000.00 [/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"] $ 50,000.00 [/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"]2021[/TD]
[TD="align: right"]2022[/TD]
[TD="align: right"]2023[/TD]
[TD="align: right"]2024[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"] $ 75,000.00 [/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 8,333.33 [/TD]
[TD="align: right"] $ 18,333.33 [/TD]
[TD="align: right"] $ 63,333.33 [/TD]
[TD="align: right"] $ 63,333.33 [/TD]
[TD="align: right"] $ 55,000.00 [/TD]
[TD="align: right"] $ 55,000.00 [/TD]
[TD="align: right"] $ 30,000.00 [/TD]
[TD="align: right"] $ 20,000.00 [/TD]

[TD="align: center"]5[/TD]
[TD="align: right"] $ 100,000.00 [/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4[/TH]
[TD="align: left"]{=SUM(($E$2:$E$10<=H3)*($E$2:$E$10+$D$2:$D$10>=H3)*IFERROR($C$2:$C$10/$D$2:$D$10,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Bkisley]

This is very close - I think I can figure it out from here though. Thanks for getting me 99% there!

 

[Eric W]

Hmmm, yes, I should have included -1 in the second condition:

=SUM(($E$2:$E$10<=H3)*($E$2:$E$10+$D$2:$D$10-1>=H3)*IFERROR($C$2:$C$10/$D$2:$D$10,0))
with CSE.

In any case, I'm glad it works for you!
Let us know if you have any other questions.