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
1	Investment Cost	Depreciation Life	Year Depreciation Starts
2	$ 25,000.00	3	2017
3	$ 50,000.00	5	2018			2017	2018	2019	2020	2021	2022	2023	2024
4	$ 75,000.00	3	2019			$ 8,333.33	$ 18,333.33	$ 63,333.33	$ 63,333.33	$ 55,000.00	$ 55,000.00	$ 30,000.00	$ 20,000.00
5	$ 100,000.00	5	2019

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet10​

Array Formulas Cell Formula H4 {=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))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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.