Note: The other languages of the website are Google-translated. Back to English

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. Full Feature Free Trial 30-day!

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, 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:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
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!
officetab bottom
Comments (48)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How do you do this when the date range is defined in another column? E.g. I have a main worksheet with a "week ending" column and another with expenses entered on a daily basis. How can I find and sum all expenses on or up to 7 days before each week ending date? =SUMIF(expenses!A2:expenses!A1024,main!$A$2&"
This comment was minimized by the moderator on the site
i have worksheet 1 with customer list and date of order. On second work sheet i have a weekly sales report with start and end range. i would like to look up date of order and summarize sales by week if the date range falls with the start and and end range on workbook 2. I've tried combining a sumifs, vlookup with a statement and can not resolve the calculation errors.
This comment was minimized by the moderator on the site
Which formula would you use should you wish to add certain cell values should the date be within 10 days of the current date of the spreadsheet. I.E I have two date cells, 1 being an automatic date updater when spreadsheet is opened and another date cell being that of which when an entry was made. There is over 100 lines of this and would like to add just the sales within 10 days of current date.
This comment was minimized by the moderator on the site
If you need a reference to another book/worksheet, use something like CONCATENATE(">=",'GLOBAL REPORT'!E4) as the criteria.Forsome reason this is needed.
This comment was minimized by the moderator on the site
I have used your formula, but it only works if you type in the date, it is very static. If I make it look at a cell, it doesn't sum. What am I doing wrong.=SUMIFS(B2:B8,A2:A8,">=e4 ",A2:A8,"
This comment was minimized by the moderator on the site
I was having same issue but found another website that showed the answer:we write =SUMIFS($C$5:$C$95,$B$5:$B$95,">="&$F$5,$B$5:$B$95,"
This comment was minimized by the moderator on the site
Need formula for subtotal of "A" within date range. (total $ collected from "A" between 4/1 and 4/6, total $ collected from "B" between 4/1 and 4/16)
This comment was minimized by the moderator on the site
I am successful using the above formula ( =SUMIFS(B2:B8,A2:A8,">="&E2,A2:A8,"
This comment was minimized by the moderator on the site
Good day, Info to note * working with 10 products (Prices vary every day) prices in worksheet 1-"Prices" (Colum A dates) (Products Row 2C-M) and quantity sold for the day in sheet 2 "Book 34" (each book consist of 100 invoices (1-100 in row B dates in A) products in row 2 (C-M) as well. * should I add a product the formula needs to identify with a formula not with an amount Example. I need to type the date and the quantity in each product and the total formula will identify with the product and date and multiply together. Please assits
This comment was minimized by the moderator on the site
Hi, I need a formula that will display a total if the start and finish dates are between dates shown in a row. I have tried various IF( AND...) formulas but get caught out on either the start or finish dates
This comment was minimized by the moderator on the site
Hi, I require a formula which is kind of the reverse of the =SUMIFS(B2:B8,A 2:A8,">="&E2,A2 :A8,"
This comment was minimized by the moderator on the site
hi i want to apply this formula on other sheet while my values and date are on other sheet how could this be possible please
This comment was minimized by the moderator on the site
I want the formula for exactly reverse criteria.. I want to arrive a value of a date.. I have from and to dates and value..
This comment was minimized by the moderator on the site
Thanks a bunch. Works perfectly well.
This comment was minimized by the moderator on the site
No idea why the formula is not working :( I am using it exactly. When I copy the sample data it works but, when I put in my data it fails. I never had an issue with sumifs ...weird Here is my formula....=SUMIFS('KPI Data'!$G:$G,'KPI Data'!$E:$E,">="&$C$4,'KPI Data'!$E:$E,"
This comment was minimized by the moderator on the site
you need to change ">=" to "> =". The code they provided is missing the space.
This comment was minimized by the moderator on the site
thanks bro it's working
This comment was minimized by the moderator on the site
DATED wgt
09-05-2017 0.71
30-05-2017 0.55
07-06-2017 0.53
07-06-2017 0.50
07-06-2017 0.36
07-06-2017 1.01
08-06-2017 0.80
13-06-2017 0.51
13-06-2017 0.50
13-06-2017 1.02
14-06-2017 3.01
15-06-2017 1.00
15-06-2017 0.71
19-06-2017 0.54
24-06-2017 1.40
26-06-2017 1.61
27-06-2017 2.53
30-06-2017 0.70
04-07-2017 0.30
04-07-2017 0.30
04-07-2017 1.30
04-07-2017 0.50
05-07-2017 0.61
10-07-2017 1.01
10-07-2017 0.60
10-07-2017 0.70
12-07-2017 0.50

i want to calculate date wise weight and convert to month



Total month
May -
June -
July -
This comment was minimized by the moderator on the site
Hi Hardik,
Do you mean summing by month? Methods introduced in this article may help you:
https://www.extendoffice.com/documents/excel/3181-excel-sum-by-week-month-quarter-year.html
This comment was minimized by the moderator on the site
=SUMIFS(Expenses!D6:K300,Expenses!C6:C300,"> ="&References!B5,Expenses!C6:C300,"< ="&References!C5)

returns #VALUE. Where am I going wrong? Expense tab, column C has dates. Expenses tab columns D thru K have the $$ amounts that need to be summed. References tab B5 has beginning date. References tab C5 has ending date.
This comment was minimized by the moderator on the site
Hi Sharon,
There are two things you need to pay attention:
(1) Remove space between > and =
(2) Check if the dates are not stored as text. If stored as text, convert them to date/number.
This comment was minimized by the moderator on the site
If I wanted to incorporate another IF into this formula how would I do it? My formula looks like this:

=SUMIFS(E2:E51,C2:C51,">="&C52,C2:C51,"<="&AH52)
I also want to include a variable in CELL D52 from the range of D2:D51.
This comment was minimized by the moderator on the site
Hi Darren,
If you are not familiar with the SUMIFS function, you can click Formulas > Insert Functions, type SUMIFS in the search for a function box, and click the Go button, and then specify as much criteria as you need in the Function Arguments dialog box. See screenshot:
This comment was minimized by the moderator on the site
Can you do like this
=SUMIFS(E2:E51,C2:C51,">="&C52,C2:C51,"<="&AH52,D2:D51,D52)
This comment was minimized by the moderator on the site
Very helpful, thanks!
This comment was minimized by the moderator on the site
Hi there,


I want to use a IF formula to pick up the number in a cell, only if the dates in which it rates to fall between two dates. Eg if the start date is between the two dates, then pick up a number from the cell.



Help
This comment was minimized by the moderator on the site
Hi Accountant,
For example:
A1 is the first date 2018/2/27, A2 is the last date 2018/4/13, and you will see if the date in C1 falls in the date range. In this case, you can use this formula =IF(AND(C1>A1,C1
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations