Summing for Date Ranges

[pdvsa]

Hello Excel Guru's,

I cant seem to sum the costs associated with these date ranges.
Grateful for your assistance.

thank you in advance. There are 2 sheets. Please scroll down for the date ranges.
I have this in one sheet:

		1/2/2020​	11/29/2030​	1/31/2026​	1/31/2046​
		$ 2.00	$ 6.00	$ 8.00	$ 10.00
	Totals:
Pre FC Date	?	this would be a sum from Pre FC Date up to but not including Financial Close Date as found on the Key Dates sheet
Financial Close Date	?	this would be a sum from Financial Close Date up to but not including Construction End Date as found on the Key Dates sheet
Construction End Date	?	this would be a sum from Construction End Date up to but not including Operations End Date as found on the Key Dates sheet
Operations End Date	?	this would be a sum from Construction End Date up to Operations End Date as found on the Key Dates sheet

In the Key Dates Sheet:

Pre FC Date	1/1/2020​
Financial Close Date	11/30/2023​
Construction End Date	1/31/2026​
Operations End Date	1/31/2046​

 

[jtakw]

Hi,

Is 11/29/2030 in your First row correct?
As it's not in "sync" with the way you have all your other dates, which are in chronological order.

 

[pdvsa]

Hi,

Is 11/29/2030 in your First row correct?
As it's not in "sync" with the way you have all your other dates, which are in chronological order.

Hi, actually it as not correct. I dont know how that happened. It was a copy and paste.
11/29/2030 =11/29/2023. The others are correct. Sorry about that. That is odd indeed.

 

[jtakw]

Ok,

See if this does what you want.
Add Sheet Name, Change/Adjust Cell references/Range as needed.

Book3.xlsx
	A	B	C	D	E	F
1			1/2/2020	11/29/2023	1/31/2026	1/31/2046
2			$2.00	$6.00	$8.00	$10.00
3
4		Totals:
5	Pre FC Date	8	this would be a sum from Pre FC Date up to but not including Financial Close Date as found on the Key Dates sheet
6	Financial Close Date	0	this would be a sum from Financial Close Date up to but not including Construction End Date as found on the Key Dates sheet
7	Construction End Date	8	this would be a sum from Construction End Date up to but not including Operations End Date as found on the Key Dates sheet
8	Operations End Date	18	this would be a sum from Construction End Date up to Operations End Date as found on the Key Dates sheet
9
10
11	Pre FC Date	1/1/2020
12	Financial Close Date	11/30/2023
13	Construction End Date	1/31/2026
14	Operations End Date	1/31/2046
Sheet925

Cell Formulas
Range	Formula
B5:B7	B5	=SUMIFS(C$2:F$2,C$1:F$1,">="&B11,C$1:F$1,"<"&B12)
B8	B8	=SUMIFS(C$2:F$2,C$1:F$1,">="&B13,C$1:F$1,"<="&B14)

 

[Michael M]

Dont have Excel at the moment, so UNTESTED

=SUMIFS(C4:F4,C3:F3,"> "&Key Dates!B2,C3:F3,"< "&Key Dates!B3)

 

[pdvsa]

Nice. Since I have the Key Dates in another sheet, I need to reference that sheet. when I do this, I get an excel msg box that it wants to open a file. Do you know how to change the formula to reference the dates on another sheet named "Key Dates"? I might need quotes or something like that around the name of the sheet. I am not sure though. Appreciate your expert assistance.

 

[jtakw]

Not sure if you're talking to me, or Michael.

Book3.xlsx
	A	B	C	D	E	F
1			1/2/2020	11/29/2023	1/31/2026	1/31/2046
2			$2.00	$6.00	$8.00	$10.00
3
4		Totals:
5	Pre FC Date	8	this would be a sum from Pre FC Date up to but not including Financial Close Date as found on the Key Dates sheet
6	Financial Close Date	0	this would be a sum from Financial Close Date up to but not including Construction End Date as found on the Key Dates sheet
7	Construction End Date	8	this would be a sum from Construction End Date up to but not including Operations End Date as found on the Key Dates sheet
8	Operations End Date	18	this would be a sum from Construction End Date up to Operations End Date as found on the Key Dates sheet
Sheet925

Cell Formulas
Range	Formula
B5:B7	B5	=SUMIFS(C$2:F$2,C$1:F$1,">="&'Key Dates'!B1,C$1:F$1,"<"&'Key Dates'!B2)
B8	B8	=SUMIFS(C$2:F$2,C$1:F$1,">="&'Key Dates'!B3,C$1:F$1,"<="&'Key Dates'!B4)

Book3.xlsx
	A	B
1	Pre FC Date	1/1/2020
2	Financial Close Date	11/30/2023
3	Construction End Date	1/31/2026
4	Operations End Date	1/31/2046
Key Dates

 

[pdvsa]

Not sure if you're talking to me, or Michael.

Book3.xlsx
	A	B	C	D	E	F
1			1/2/2020	11/29/2023	1/31/2026	1/31/2046
2			$2.00	$6.00	$8.00	$10.00
3
4		Totals:
5	Pre FC Date	8	this would be a sum from Pre FC Date up to but not including Financial Close Date as found on the Key Dates sheet
6	Financial Close Date	0	this would be a sum from Financial Close Date up to but not including Construction End Date as found on the Key Dates sheet
7	Construction End Date	8	this would be a sum from Construction End Date up to but not including Operations End Date as found on the Key Dates sheet
8	Operations End Date	18	this would be a sum from Construction End Date up to Operations End Date as found on the Key Dates sheet
Sheet925

Cell Formulas
Range	Formula
B5:B7	B5	=SUMIFS(C$2:F$2,C$1:F$1,">="&'Key Dates'!B1,C$1:F$1,"<"&'Key Dates'!B2)
B8	B8	=SUMIFS(C$2:F$2,C$1:F$1,">="&'Key Dates'!B3,C$1:F$1,"<="&'Key Dates'!B4)

Book3.xlsx
	A	B
1	Pre FC Date	1/1/2020
2	Financial Close Date	11/30/2023
3	Construction End Date	1/31/2026
4	Operations End Date	1/31/2046
Key Dates

Great! I was trying double quotes. I see its a single. Thank you. Excellent.

i didnt see the response from Michael. Sorry about that Michael.

 

[jtakw]

You're welcome.

Please note. the formula for "Operations End Date" is slightly different than the other 3, which is just copied down.