• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

SUMIF Between Two Dates

Home » Excel » Maths Function in Excel » SUMIF Between Two Dates

By Sharmila Reddy Leave a Comment

Excel Sumif Between Two Dates

When we work with data which have serial number with different dates and the condition to sum the values is based between two dates, we need to specify the conditions for the dates, the first date will be obviously smaller than the last date so we can use <= operator and >= operator to sum the values between the dates.

Excel Sumif Between Two Dates

With Excel, it becomes quite easy to add or subtract numeric value between two dates as a criteria/condition. We can add or subtract values between two certain dates using two functions: ‘SUMIF’ and ‘SUMIFS’.

General Syntax for ‘SUMIF’ function is as follows:

SUMIF Formula

The SUMIF function syntax has the following arguments:

  • range: Required, represents the range of cells we wish to apply the criteria against
  • criteria: Required, represents the condition or criteria to be met. This could be supplied in the form of a number, date, text, cell reference, logical expression, or any other Excel function
  • sum_range: Optional represents the cells to sum if the criteria are met. This argument needs to be mentioned only when we wish to sum cells other than defined in the range argument. If omitted, the function will sum the same cells to which the condition/criteria is applied.

When multiple criteria are to be specified in the ‘SUMIF’ function, then this is achieved using logical/comparison operators. Since we need to sum or subtract cell values lying in between two dates, thus both dates (mentioned as condition/criteria) have to be tested on the same range. Hence, multiple ‘SUMIF’ functions are applied to subtract or add the resulting values in one formula.

Specific date criteria would be mentioned in each ‘SUMIF’ function, and both functions would be then combined in one formula so as to get the final value via subtracting or adding the resulting values of each function.

This would look like the below syntax:

SUMIF (range, criteria1, [sum_range]) - SUMIF (range, criteria2, [sum_range]) 

The starting date would be criteria1, and ending date would be criteria2.

Examples of Sumif Between Two Dates and other criteria in Excel

Following are the examples of Sumif Between Two Dates:

You can download this Sumif Between Two Dates Excel Template here – Sumif Between Two Dates Excel Template

Example #1

If we have a table consisting of two columns: one containing dates and one containing the value of transaction done.  So if we wish to sum the transactions that are done after the date: 15/01/2019, and those that are done before the date: 20/03/2019, i.e sum transactions if the corresponding date is between 15/01/2019 and 20/03/2019.

Sumif Between dates Example 1

Then we use the sumif function. This is done by applying the following formula:

Popular Course in this category
Cyber Monday Sale
All in One Excel VBA Bundle (35 Courses with Projects) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,353 ratings)
Course Price

View Course

Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification Course
=SUMIF($A$2:$A$6,”>”$E$2,”$B$2:$B$6) - SUMIF($A$2:$A$6,”<”$E$3,”$B$2:$B$6)

We can see that the first SUMIF function contains the start date as criteria with logical expression ‘greater than’ and cell reference (that is cell E2), combined with an ‘&’ sign, and the second SUMIF function contains the end date as criteria with logical expression ‘less than’ and cell reference ( that is cell E3), combined with an ‘&’ sign. The range argument and the sum_range argument provided in both the SUMIF are same.

Sumif Between dates Example 1-1

So we see that the first SUMIF will sum all transaction values where the corresponding date is greater than the start date (15/01/2019), and the second SUMIF will sum all transaction values where the corresponding date is less than the end date (20/03/2019). After this, the two resultant values are subtracted to get the final value.

Following is this illustration:

Example 1-2

The highlighted values are added (10,000+5,000+7,000+15,000=37,000) to get 37,000 with the first SUMIF. This is so because these are the cells that satisfy the first criteria, i.e these transaction amounts are done after the start date: 15/01/2019.

This value (37,000) is then subtracted to the sum of below-highlighted cells (5,000+20,000+7,000=32,000) to get 32,000 (or cells that get added with the second SUMIF as these are the cells that satisfy the second criteria, i.e these transaction amounts are done before the end date: 20/03/2019).

Sumif Between dates Example 1-3

So, final value= 37,000-32,000=5,000

Sumif Between dates Example 1-3

Example #2

If we have a table consisting of two columns: one containing dates and one containing the number of assignments submitted on that date.  So if we wish to sum the number of assignments that are done after the date: 15/01/2019, and those that are done before the date: 20/03/2019.

Sumif Between dates Example 2

Then we use a sumif function. This is done by applying the following formula:

=SUMIF($A$2:$A$6,”>”$E$2,”$B$2:$B$6) - SUMIF($A$2:$A$6,”<”$E$3,”$B$2:$B$6)

So we see that the first SUMIF will sum all the number of assignments where the corresponding date is greater than the start date (15/01/2019), and the second SUMIF will sum all the number of assignments where the corresponding date is less than the end date (20/03/2019). After this, the two resultant values are subtracted to get the final value.

Example 2-1

Following is this illustration:

Example 2-2

The highlighted values are added (12+5+7+15=39) to get 39 with the first SUMIF. This is so because these are the cells that satisfy the first criteria, i.e these number of assignments are submitted after the start date: 15/01/2019.

This value (39) is then subtracted to the sum of below-highlighted cells (5+20+7=32) to get 32 (or cells that get added with the second SUMIF as these are the cells that satisfy the second criteria, i.e these number of assignments are submitted before the end date: 20/03/2019).

Example 2-3

So, final value= 39-32=7.

Sumif Between dates Example 2-4

Things to Remember About SUMIF Between Two Dates in Excel

  • The ‘SUMIF’ function in Excel is a built-in function that is categorized as a Mathematical/Trigonometry function.
  • The ‘SUMIF’ function usually returns a numeric value.
  • The sum_range and range arguments provided as an argument to the function should be ranged, and not arrays.
  • Any criteria consisting of mathematical symbols or text criteria mentioned as an argument in the function must be enclosed in double quotation marks.
  • The sum-range argument criteria mentioned as an argument in the function need not necessarily be of the same size as the range argument, however in the case where we are using SUMIF between two dates, then the sub-range argument criteria mentioned as an argument in the function has to be of the same size as the range argument.
  • The ‘SUMIF’ function in Excel can also be used as a worksheet function. As a worksheet function, it can be entered as part of a formula in a cell of a worksheet.
  • If sum_range is omitted, then the actual cells that are added are those that are provided as a range argument to the function.
  • SUMIFS function allows to impose or use more than one criteria without the use of logical/comparison operators.
  • SUMIF function can reference other workbooks or worksheets only if they are currently open.

Recommended Articles

This has been a guide to Sumif Between Two Dates and another criteria in excel. Here we discuss the difference between two dates using SUMIF Function in Excel with some examples and downloadable excel template. You may learn more about excel from the following articles –

  • Excel Text SumIF
  • Excel SUM Shortcuts
  • How to use VLOOKUP Tutorial in Excel? (with Examples)
  • SUMIF on VLOOKUP
  • Using VLOOKUP with SUM
  • SUMPRODUCT Excel Formula
  • DSUM in Excel
  • Using SUMPRODUCT with Multiple Criteria
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: Excel, Maths Function in Excel

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Download Sumif Between Two Dates Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

CYBER WEEK OFFER - All in One Excel VBA Bundle (35 Courses with Projects) View More