Skip to main content

How to sum values between two dates in Excel?

When there are two lists in your worksheet as right screenshot shown, one is the list of dates, the other is the list of the values. And you want to sum up the values between two dates range only, for instance, sum up the values between 3/4/2014 and 5/10/2014, how can you calculate them quickly? Now, I introduce a formula for you to sum up them in Excel.


Sum values between two dates with formula in Excel

Fortunately, there is a formula that can sum up the values between two dates range in Excel.

Select a blank cell and type into below formula, and press Enter button. And now you will get the calculating result. See screenshot:

=SUMIFS(B2:B8,A2:A8,">="&E2,A2:A8,"<="&E3)

Note: In above formula,

  • D3:D22 is the value list you will sum up
  • B3:B22 is the date list you will sum based on
  • G3 is the cell with start date
  • G4 is the cell with end date
note ribbon Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future!
Read more…     Free trial

Easily sum data in every fiscal year, every half year, or every week in Excel

The PivotTable Special Time Grouping feature, provided by Kutools for Excel, is able to add a helper column to calculate the fiscal year, half year, week number, or day of week based on the specified date column, and let you easily count, sum, or average columns based on the calculated results in a new Pivot Table.


Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now


Sum values between two dates with filter in Excel

If you need to sum values between two dates, and the date range changes frequently, you can add filter for the certain range, and then use the SUBTOTAL function to sum between the specified date range in Excel.

1. Select a blank cell, enter below formula, and press the Enter key.

=SUBTOTAL(109,D3:D22)

Note: In above formula, 109 means sum filtered values, D3:D22 indicates the value list you will sum up.

2. Select the range title, and add filter by clicking Data > Filter.

3. Click the filter icon in the Date column header, and select Date Filters > Between. In the Custom AutoFilter dialog, type the start date and end date as you need, and click the OK button. The total value will change automatically based on filtered values.


Related articles:

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

kte tab 201905


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (48)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
INVOICE DATE TOTAL
01-09-2021 19817
02-09-2021 44791
03-09-2021 26780
03-09-2021 58500
03-09-2021 2029

04-09-2021 79260
04-09-2021 2120
04-09-2021 9187
04-09-2021 5698
04-09-2021 1400
04-09-2021 1900
04-09-2021 7500
04-09-2021 830


i have above data in one sheet and i need date wise total value as below in another sheet,01-09-2021 19817
02-09-2021 44791
03-09-2021 87309
04-09-2021 107895

pls give formula details for itsanjay
This comment was minimized by the moderator on the site
Hi Jett,
You can use the SUMIFS function to sum by multiple criteria simultaneously.
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2,…, criteria_rangeN, criteriaN)
This comment was minimized by the moderator on the site
What if there are multiple columns to sum up like one for interest, one for penalty, and one for principal amount? and it still need to be between two dates
This comment was minimized by the moderator on the site
hi i have a question related to your topic,if the case is like thisDATE AMOUNT17 SEP 70.000 60.00018 SEP 30.00019 SEP 20.000 10.000IF i use sum if between date 17 sept to 19 sept the 60.000 & 10.000 can't be include in the total.what formulas should i use to include the 60.000 and 10.000 in the total?
This comment was minimized by the moderator on the site
Hi, i have a question related to sumifs:

want to calculate a sum between two dates e.g. start of date and end of date, but at times there are payments which were made in a new month but connected to the expense month, For example if a payment date is Oct 5th but the payment is for September and I would like to show this payment for the month of September, the formula should ignore the Oct 5th date and consider the payment in September. How do i do that
This comment was minimized by the moderator on the site
Help! I can only get this formula to work if I convert to a MM/DD/YYYY format but all of my data is DD/MM/YYYY. The cells are formatted correctly but the formula doesn't work unless I change from eg. 14/01/2020 to 01/14/2020.
This comment was minimized by the moderator on the site
Hi,

can you help me to calculate two working experiences in excel.

Ex: 12 Yeas 3 Months 5 Days - CDB Finance company

9 Years 4 Months 7 Days - Commercial Bank

These are two working experiences in two working places of one person. how to sum these two experiences as a total experience in excel. Please help me.
This comment was minimized by the moderator on the site
Hi Ayesha,
We can suppose the start is 1900/1/0, and add the two date periods to get the end date, and then get the difference between the start date and end date as attached image shown.
Two formulas used:
In Cell B7: =DATE(YEAR(0)+B4+B5,MONTH(0)+C4+C5,DAY(0)+D4+D5)
In Cell B9: =DATEDIF(B1,B7,"Y")&" Years, "&DATEDIF(B1,B7,"YM")&" Months, "&DATEDIF(B1,B7,"MD")&" Days"
This comment was minimized by the moderator on the site
Hi all,

What if one would like to make a date range non-mandatory?

Meaning, let's say that I have two cells with dates in them that are used as my beginning and end date threshold, but when those cells aren't populated, the sum for all dates is pulled from the data set.

My formula (below) has two match criteria with the date ranges at the back of the formula. What I'm trying to do is only filter the sum by the date range if a condition is met (ideally a cell that acts as select Yes or No on "Do you want to limit the sum to a specific date range?"

=SUMIFS(BURN_DATA!$S:$S,BURN_DATA!$R:$R,$A12,BURN_DATA!$H:$H,I$3,BURN_DATA!O:O,">="&I9,BURN_DATA!O:O,"<="&J9)


THANKS IN ADVANCE!
This comment was minimized by the moderator on the site
Aloha, I'm trying to figure out how to sum another column of units, when one column of dates is sequential. Example: I have rows with dates, Mar 1, Mar 2, mar3, then marc 6,7, then march 10.
I need to sum the corresponding units in of another column for mar 1-3, mar 6-7 and mar 10.
This comment was minimized by the moderator on the site
Hi David,
You can use methods introduced in this webpage to sum each period one by one.
Or you can add a helper column before the date column as attached image shown, and then apply the Subtotal feature.
This comment was minimized by the moderator on the site
I am trying to create a holiday calendar to calculate the following information:

Start Date:
End Date (if there is one, otherwise calculate on the entire holiday year_
No of Holidays
Holiday year runs from 01/04 to 31/03 with an entitlement of 20 days plus bank holidays

any help is much appreciated
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations