EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Excel Excel Resources Maths Function in Excel Sumif Between Two Dates

Sumif Between Two Dates

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated May 9, 2023

Sumif Between Two Dates

Sumif Between Two Dates

Sumif between the two dates is a useful and easy function. By this, we can calculate the total of any specified range between dates. This allows us to get a specific sum of any interval data. For calculating the sum between two specific dates, we will use the Sumifs function.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

To calculate the total of any specified criteria and range in Excel, you can use the Sumifs function. The major difference between Sumif and Sumifs is that in Sumifs, we have to use and add more than one criteria” s in a single function argument instead of adding a separate syntax in the same cell.

sumifs function can be accessed from Insert Function from the Math & Trig category.

How to Use Sumif Between Two Dates in Excel?

Sumif between two dates in Excel is very simple and easy to use. Let us now see how to use the Sumif between two dates in Excel with the help of some examples.

Sumif Between Two Dates in Excel – Example #1

We have owner wise sales data for some products, as shown below.

Example 1-1

As we can see, the Date of Order column has different dates on which the product was sold. Now with the help of Sumifs, we need to calculate the sum of the product sold between two dates. For this, we must select two dates between the dates mentioned in the Date of Order columns in separate cells.

As shown in the screenshot below, we have taken 3/19/2019 and 3/5/2019, which are in the middle portion.

sumifs between two dates example 1-2

Before we learn to calculate the sum between two dates, let’s understand the complete syntax of the Sumifs function, which we will use.

Excel sumifs image

The syntax of Sumifs is explained below;

  • Sum_Range = This is the sum range or date that must be added.
  • Criteria_Range1 = This is the first criteria range we need to get the sum. There may be more than 1 criterion.
  • Criteria 1 = This is the first criterion for seeing the sum output. For this as well, there can be more than 1 criterion.

Now let’s apply Sumifs and calculate the sum between the two dates. For this, go to the cell where we need to see the output, type the “=” (Equal) sign, search, and select the SUMIFS function. Now select the defined criteria and range as explained above.

Select the Sum Range as D2 to D20 and Criteria Range 1 as B2 to B20. Now for criteria1, add “<=”&G2 (After Date 3/19/2019). In the next phase of the same syntax, select the criteria Range2 again as B2 to B20, and for criteria2, add “>=” &G3 (Before Date 3/5/2019) as shown below.

Note: Ampersand (&) in criteria 1 and criteria 2 is used for concatenating “<=” and “>=” signs with dates.

Example 1-3

Once done, press enters to see the result. As shown below, the Sumifs function has calculated the sum between 3/19/2019 and 3/5/2019 as 392.

Example 1-4

To test the obtained result, let’s change the dates between the selected dates range. As we see below, we can still get the result between the selected after and before dates even when we change the dates.

sumifs between two dates 1-5

Sumif Between Two Dates in Excel – Example #2

There is a way to use the same syntax a little differently. Here is the syntax used previously.

=SUMIFS(D2:D20,B2:B20,”<=”&G2,B2:B20,”>=”&G3)

Example 1-1

Now let us change the way we wrote and framed the syntax. In the above syntax, in place of criteria 1 and 2, we have selected the cells with the dates. And for this, we will type the dates between which we need to calculate the sum using sumifs, as shown below.

sumifs between two dates 2-2

As we can see in the above screenshot, we have entered the After date as of 3/14/2019 and the Before date as of 2/28/2019. Between these dates, we will calculate the sum of the quantity sold out. To get the result, press enter.

Once we do that, we will get the sum of the quantity sold between 3/14/2019 and 2/28/2019 as 611.

sumifs between two dates 2-3

Considering the After date first and Before date last or using the correct criteria if we change the date is significant. For testing, let’s interchange the date sequence by taking the Before date first and the After date last, as shown below.

sumifs between two dates 2-4

As we can see, we got “0”. This means the data cannot be summed as the selected range gives criteria 1 and criteria 2 both have dates. Hence, we got “0” as a result.

sumifs between two dates 2-5

Pros of Sumif Between Two Dates

  • This straight-through gives the result between selected dates.
  • Syntax of the Sumifs looks complex, but implementation is easy.
  • After and Before dates can also be replaced with Today’s date if the data is of that kind.

Cons of Sumif Between Two Dates

  • Sometimes people may unknowingly replace or interchange the before and after dates, so in this case, the obtained result will be “0”.

Things to Remember 

  • Always select the Before and After dates in sequence. In the first half of the syntax, select After the date and then select Before the date in the second half.
  • A cell containing dates or manually entered dates give the same result.
  • We can use a single date, which will give the result respected to that specific date only, which will not be the sum of any data set.
  • Use ampersand to concatenate the dates with criteria, as dates are chosen by selecting the specific cells.

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

Recommended Articles

This has been a guide to Excel Sumif Between Two Dates. Here we discuss How to use Sumif Between Two Dates in Excel, examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. SUMIF in Excel
  2. SUMIF Formula in Excel
  3. Excel SUMIFS with Dates
  4. Excel SUMIF with OR
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Blog as Guest
Courses
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
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

EDUCBA

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

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

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW