Formula help

[joercav]

Farm spreadsheet. Struggling with a formula. Any help would be appreciated.
I have a sheet that lists daily livestock nutrition needs with start and end dates. The list is long and has overlapping ranges. On another sheet I would like to summarize the quantity of nutrition needed for a given period, for example monthly.

The formula I need would be on the second sheet and would multiply the number of days of each row on the first sheet that occur in the specified date range, with the quantity of nutrition needed and then sum all of those quantities together.

I simplified the sheets to show an example.

Truly appreciative for any help. I’ve put a stupid number of hours on this and haven’t made any progress. Only solutions I’ve come up with are incredibly clunky.

 

[JamesCanale]

My mac is at home, and I hope you have a later version of excel...

MrExcelPlayground6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Start	End	TDN/day	CP/day						TDN	CP
2	11/18/2021	12/16/2021	1962	303				4/1/2022	4/30/2022	69253	10718	134	135	4/1/2022	12/16/2021	0	0	69253
3	4/7/2022	5/5/2022	3011	466				5/1/2022	5/31/2022	316155	1864	-6	-5	4/7/2022	4/30/2022	23	69253
4	7/17/2022	8/14/2022	4383	678				6/1/2022	6/30/2022	0	0	-107	-106	7/17/2022	4/30/2022	0	0
5	11/24/2022	12/22/2022	3561	551				7/1/2022	7/31/2022	61362	9492	-237	-236	11/24/2022	4/30/2022	0	0
6	3/2/2023	3/30/2023	2938	454				8/1/2022	8/31/2022	1630476	8814	-335	-334	3/2/2023	4/30/2022	0	0
7	6/1/2023	6/29/2023	3350	518				9/1/2022	9/30/2022	0	0	-426	-425	6/1/2023	4/30/2022	0	0
8	7/11/2023	8/8/2023	3533	546								-466	-465	7/11/2023	4/30/2022	0	0
9	8/8/2023	9/5/2023	3066	474								-494	-493	8/8/2023	4/30/2022	0	0
10	10/17/2023	11/14/2023	2719	420								-564	-563	10/17/2023	4/30/2022	0	0
11	12/1/2023	12/29/2023	2468	382								-609	-608	12/1/2023	4/30/2022	0	0
Sheet32

Cell Formulas
Range	Formula
I2:I7	I2	=EOMONTH(H2,0)
J2:J7	J2	=SUM(IF(IF((H2-$A$2:$A$11)>0,$B$2:$B$11,I2)-IF((H2-$A$2:$A$11)>0,H2,$A$2:$A$11)>0,IF((H2-$A$2:$A$11)>0,B2:B11,I2)-IF((H2-$A$2:$A$11)>0,H2,$A$2:$A$11),0)*$C$2:$C$11)
K2:K7	K2	=SUM(IF(IF((H2-$A$2:$A$11)>0,$B$2:$B$11,I2)-IF((H2-$A$2:$A$11)>0,H2,$A$2:$A$11)>0,IF((H2-$A$2:$A$11)>0,$B$2:$B$11,I2)-IF((H2-$A$2:$A$11)>0,H2,$A$2:$A$11),0)*$D$2:$D$11)
L2:L11	L2	=(H2-$A$2:$A$11)
M2:M11	M2	=I2-$B$2:$B$11
N2:N11	N2	=IF(L2#>0,H2,A2:A11)
O2:O11	O2	=IF(L2#>0,B2:B11,I2)
P2:P11	P2	=IF(O2#-N2#>0,O2#-N2#,0)
Q2:Q11	Q2	=P2#*C2:C11
R2	R2	=SUM(Q2#)
H3:H7	H3	=I2+1
Dynamic array formulas.

The solution is in columns J and K. But that is mostly incomprehensible, so I've included L-R to show the breakdown of the calculations.

 

[joercav]

Thanks for the help! For some reason I’m not able to duplicate the formula on my computer. Probably the wrong version. Appreciate the assist anyways, helped me figure out an alternative.