SUM between two periods

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
1,538
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
I want to sum two periods
Sum Highlighted in Green based on Start Date in G2 and End Date H2

Book3
CDEFGH
1Start DateEnd Date
2Start DateEnd DateCapacity10/12/20202/11/2021
37/12/20209/11/20200
49/12/202010/11/202020
510/12/202011/11/202040
611/12/202012/11/202070
712/12/20201/11/202190
81/12/20212/11/2021100
92/12/20213/11/2021100
Sheet1


Expected Result as below

I want Total Capacity Between Above two periods when I change the dates formula should be updated

Book3
CDEFGH
1Expected Result
2Start DateEnd Date
3Start DateEnd DateCapacity10/12/20202/11/2021
47/12/20209/11/20200300
59/12/202010/11/202020
610/12/202011/11/202040
711/12/202012/11/202070
812/12/20201/11/202190
91/12/20212/11/2021100
102/12/20213/11/2021100
Sheet2
Cell Formulas
RangeFormula
G4G4=SUM(E6:E9)
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
If you don't have SUMIFS, you can use SUMPRODUCT:

Book1
ABCDEF
1Start DateEnd Date
2Start DateEnd DateCapacity10/12/20202/11/2021
37/12/20209/11/20200
49/12/202010/11/202020Capacity:300
510/12/202011/11/202040300
611/12/202012/11/202070
712/12/20201/11/202190
81/12/20212/11/2021100
92/12/20213/11/2021100
Sheet3
Cell Formulas
RangeFormula
F4F4=SUMPRODUCT(C3:C9,--(A3:A9>=E2),--(B3:B9<=F2))
F5F5=SUMIFS(C:C,A:A,">="&E2,B:B,"<="&F2)
 
Upvote 0
Solution
If you don't have SUMIFS, you can use SUMPRODUCT:

Book1
ABCDEF
1Start DateEnd Date
2Start DateEnd DateCapacity10/12/20202/11/2021
37/12/20209/11/20200
49/12/202010/11/202020Capacity:300
510/12/202011/11/202040300
611/12/202012/11/202070
712/12/20201/11/202190
81/12/20212/11/2021100
92/12/20213/11/2021100
Sheet3
Cell Formulas
RangeFormula
F4F4=SUMPRODUCT(C3:C9,--(A3:A9>=E2),--(B3:B9<=F2))
F5F5=SUMIFS(C:C,A:A,">="&E2,B:B,"<="&F2)

Thank you very much Eric!

Works Perfectly.

Message #1 was my 100th message in this forum!
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top