Calculating total daily spend based on start and end dates

[harooneyp]

Hello,

I have multiple ad campaigns running with different start dates, end dates, duration and budget throughout the year.

I would like to see the total daily spend (based from the specific start dates and end dates for cumulative campaigns). I've attached an image for reference (Column I has no formulas, was done manually for demonstration purposes).

Appreciate in the help in advance. Cheers!

 

[aRandomHelper]

Welcome to the forum!

Probably with SUMIFS?

Book1.xlsm
	B	C	D	E	F	G	H	I	J
1	Start date	End date			Spend			01-01-21	90
2	01-01-21	08-01-21			90			02-01-21	90
3	06-01-21	15-01-21			180			03-01-21	90
4	14-01-21	18-01-21			500			04-01-21	90
5								05-01-21	90
6								06-01-21	270
7								07-01-21	270
8								08-01-21	270
9								09-01-21	180
10								10-01-21	180
11								11-01-21	180
12								12-01-21	180
13								13-01-21	180
14								14-01-21	680
15								15-01-21	680
16								16-01-21	500
17								17-01-21	500
18								18-01-21	500
19								19-01-21	0
20								20-01-21	0
Sheet1

Cell Formulas
Range	Formula
J1:J20	J1	=SUMIFS(F:F,B:B,"<="&I1,C:C,">="&I1)

 

[harooneyp]

Welcome to the forum!

Probably with SUMIFS?

Book1.xlsm
	B	C	D	E	F	G	H	I	J
1	Start date	End date			Spend			01-01-21	90
2	01-01-21	08-01-21			90			02-01-21	90
3	06-01-21	15-01-21			180			03-01-21	90
4	14-01-21	18-01-21			500			04-01-21	90
5								05-01-21	90
6								06-01-21	270
7								07-01-21	270
8								08-01-21	270
9								09-01-21	180
10								10-01-21	180
11								11-01-21	180
12								12-01-21	180
13								13-01-21	180
14								14-01-21	680
15								15-01-21	680
16								16-01-21	500
17								17-01-21	500
18								18-01-21	500
19								19-01-21	0
20								20-01-21	0
Sheet1

Cell Formulas
Range	Formula
J1:J20	J1	=SUMIFS(F:F,B:B,"<="&I1,C:C,">="&I1)

Thanks for the reply! The formula helped a bunch.

However now there's an issue: The formula you provided is adding an extra day (including start date AND end date when it shouldn't) for each of the campaigns. Any idea?

Cheers in advance.

 

[harooneyp]

Thanks for the reply! The formula helped a bunch.

However now there's an issue: The formula you provided is adding an extra day (including start date AND end date when it shouldn't) for each of the campaigns. Any idea?

Cheers in advance.

@aRandomHelper See attached

 

[aRandomHelper]

On which day should it not include?
23rd jan or 7th feb?

Try changing either the " >= " or " <= " to " > " or " < " accordingly.
=SUMIFS(F:F,B:B,"<="&I1,C:C,">="&I1)


this post was so difficult to type out on phone..

 

[harooneyp]

On which day should it not include?
23rd jan or 7th feb?

Try changing either the " >= " or " <= " to " > " or " < " accordingly.
=SUMIFS(F:F,B:B,"<="&I1,C:C,">="&I1)


this post was so difficult to type out on phone..

Amazing, it works! Thank you so much for your help!