SUM between two periods

A Durfani

Board Regular
Joined
Apr 12, 2019
Messages
239
Office Version
  1. 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)
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,750
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)
 
Solution

A Durfani

Board Regular
Joined
Apr 12, 2019
Messages
239
Office Version
  1. 2013
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,457
Messages
5,624,853
Members
416,063
Latest member
chaulon199

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
Top