Sumif on date range, for data spread evenly in another date range

[markollington]

So I have date in the format

Value	Start Date	End Date
40	14/10/20	05/11/20
50	01/10/20	31/10/20

Assuming the value can be spread evenly over days, I want a sumif function that tells gives the following output (ie for Oct, 17 days of the 22 days of 40, PLUS 31 days of the 31 days of 50 = (17/22)*40 + 50 = 80.1

Start date	01/10/20	01/11/20
End date	31/10/20	30/11/20
Sum	81	9

Any help appreciated!

 

[StephenCrump]

Welcome to the Forum!

Perhaps like this (assuming you count both start and end dates) ...

	A	B	C
1	MyValues	Start Date	End Date
2	40	14 Oct 2020	5 Nov 2020
3	50	1 Oct 2020	31 Oct 2020
4
5	Start date	1 Sep 2020	1 Nov 2020
6	End date	31 Oct 2020	30 Nov 2020
7	Sum	81.30	8.70
8
9	Check:	81.30	8.70
Sheet1

Cell Formulas
Range	Formula
B7:C7	B7	=SUM((B5<=EndDate)*(B6>=StartDate)*(IF(B6<EndDate,B6,EndDate)-IF(B5>StartDate,B5,StartDate)+1)/(EndDate-StartDate+1)*MyValues)
B9	B9	=18/23*40+31/31*50
C9	C9	=5/23*40

Named Ranges
Name	Refers To	Cells
EndDate	=Sheet1!$C$2:$C$3	B7:C7
MyValues	=Sheet1!$A$2:$A$3	B7:C7
StartDate	=Sheet1!$B$2:$B$3	B7:C7

 

[markollington]

Amazing, thank you Stephen!