Sums from Tables based on accumulation of hours

[LeonardH]

Hello Everyone!

I'm trying to automate a big portion of my workload! For background, I've got equipment that accumulates hours over time. The month-to-month accumulation varies and is not a set interval. However, components must be replaced at set hourly intervals.

My intention is to forecast costs per month based on an expected hour accumulation. For example, take the two tables below. Table 1 shows the components, their replacement intervals, and their costs. Table 2 needs to include the formulas to reflect the sum of all costs based on the forecasted equipment hours.

Table 1
Component Name	Replacement Interval	Component Cost
Component 1	2,000	$3,000
Component 2	3,000	$7,000
Component 3	3,000	$8,500
Component 4	2,500	$4,000

Table 2
	January	February	March	April	May	June	July	August	September	October	November	December
Accumulated Equipment Hours	2543	2876	3351	3820	4352	4762	5273	5694	6150	6618	7237	7651
Cost	Sum of $ of 2,500-hour components should land here.		Sum of $ of 3,000-hour components should land here.		Sum of $ of 2,000-hour component should land here.		Sum of $ of 2,500-hour components should land here.		Sum of $ of both 2,000-hour and 3,000-hour components should land here.			Sum of $ of 2,500-hour components should land here.

Thanks so much for your help! Please let me know if you have any questions!

 

[kvsrinivasamurthy]

Give some examples and explain along with the expected results.

 

[LeonardH]

Give some examples and explain along with the expected results.

I'm not sure I'll explain it any better with this, but based on the costs associated in Table 1, formulas in the "Cost" row of Table 2 would return the values shown below:

	Table 1 (Same as original)
	A​	B​	C​
1	Component Name	Replacement Interval	Component Cost
2	Component 1	2,000	$3,000
3	Component 2	3,000	$7,000
4	Component 3	3,000	$8,500
5	Component 4	2,500	$4,000

	Table 2
	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​
1		December	January	February	March	April	May	June	July	August	September	October	November	December
2	Accumulated Equipment Hours	1999	2543	2876	3351	3820	4352	4762	5273	5694	6150	6618	7237	7651
3	Cost		$7,000		$15,500		$3,000		$4,000		$18,500			$4,000

In other words:

• January is showing $7,000 because Components 1 and 4 are expected to be replaced at 2,000 and 2,500 hours respectively. The equipment was only forecasted to achieve the required hours in January, so that is when the components are expected to be replaced, and the sum of those components needs to show.
• March is showing $15,500 because Components 2 and 3 are both expected to be replaced at 3,000 hours. The equipment wasn't forecasted to achieve 3,000 hours until March, so that's when the sum of those components needs to show.
• May is showing $3,000 because Component 1 is forecasted to achieve its second 2,000-hour interval (4,000 hours). So, the sum of components meeting that interval criteria (in this case only Component 1) is returned.
• July is showing $4,000 because Component 4 is forecasted to achieve its second 2,500-hour interval (5,000 hours). So, the sum of components meeting that interval criteria (in this case only Component 4) is returned.
• September is showing $18,500 because Component 1 is forecasted to achieve its third 2,000-hour interval (6,000 hours) and Components 2 and 3 are forecasted to achieve their second 3,000-hour interval (6,000 hours). So the sum of all those components is returned.
• December is showing $4,000 because Component 4 is forecasted to achieve its third 2,500-hour interval (7,500 hours). So, the sum of that component (or others that could potentially have met their interval criteria that month) is returned.

Ideally, only the cells in the Cost row of Table 2 should include formulas.

I hope this helps to clarify! Please let me know if there are any other questions!

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Component Name	Replacement Interval	Component Cost
2	Component 1	2000	$ 3,000
3	Component 2	3000	$ 7,000
4	Component 3	3000	$ 8,500
5	Component 4	2500	$ 4,000
6
7		January	February	March	April	May	June	July	August	September	October	November	December
8	Accumulated Equipment Hours	2,543	2,876	3,351	3,820	4,352	4,762	5,273	5,694	6,150	6,618	7,237	7,651
9	Cost	$ 7,000	$ -	$ 15,500	$ -	$ 3,000	$ -	$ 4,000	$ -	$ 18,500	$ -	$ -	$ 4,000
Sheet4

Cell Formulas
Range	Formula
B9:M9	B9	=SUMPRODUCT(QUOTIENT(B8,+$B$2:$B$5),$C$2:$C$5)-SUM($A$9:A9)