EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Maths Function in Excel Excel SUMIFS with Dates
Secondary Sidebar
Excel Functions
  • Logical Functions in Excel
    • Excel IF AND Function
    • IFERROR Formula in Excel
    • SUMIF with Multiple Criteria
    • Excel SUMIFS with Dates
    • FALSE Function in Excel
    • Excel IF Function
    • Excel Logical Test
    • Excel XOR Function
    • Logical Functions Excel
    • IFERROR Excel Function
    • TRUE Function in Excel
    • OR Function in Excel
    • NOT in Excel
    • AND Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL ADVANCED Certification
  • Excel for HR Training

Excel SUMIFS with Dates

By Madhuri ThakurMadhuri Thakur

SUMIFS With Dates in Excel

Excel SUMIFS with Dates (Table of Contents)

  • SUMIFS with Dates in Excel
  • How to Use SUMIFS with Dates in Excel?

SUMIFS with Dates in Excel

SUMIFS is an excel function used to find conditional sum with multiple conditions. Logical operators are mostly used to compare the different conditions. This is a function that adds values which meets multiple criteria. Logical operators like greater than, less than, greater than or equal to, less than or equal to, not equal to, are used to compare the different criteria. While processing a sales report or banking transactions, there will be situations to deal with dates. The SUMIFS will be used with dates in this condition. Here we may calculate the sum of product sales within a particular date or sum the sales done after a particular date etc. SUMIFS can consider as a plural form of SUMIF where single criteria will be checked in SUMIF and multiple in SUMIFS.

Start Your Free Excel Course

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

Syntax of the SUMIFS Function

SUMIFS Formula

  • Sum_range the cells to sum once the criteria are satisfied
  • Criteria_range1 the first range to be evaluated according to the criteria
  • Criteria 1 the first condition that should meet the criteria
  • Criteria_range2, criteria 2 additional range and criteria for the specified range

The result will depend on all the criteria given. If anyone is not satisfied, it will not produce a result. SUMIFS works on AND logic, so if any criteria does not match, it won’t produce a result. The text and null values won’t be counted; only numeric values will be added to give the sum.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,858 ratings)

How to Use SUMIFS with Dates in Excel?

When you have a single condition to check within a single range of cell, the SUMIF function is preferred. If the criteria are multiple and with a different range of cells, the SUMIFS function is used. Similar to the name, it will make the sum of cells or range of cells only if the conditions are satisfied. Let’s understand how to use SUMIFS with Dates in Excel with some examples.

You can download this SUMIFS with Dates Excel Template here – SUMIFS with Dates Excel Template

SUMIFS with Dates – Example #1

Below is the list of products shipped on different dates and Qty shipped. We need to find the sum of the shipped quantity for a particular period.

  • The list of the product is given in column B, Product shipped to date in column C, and Qty shipped in column D are as below.

SUMIFS With Dates Data 1

  • We need to find the Qty shipped up to 18-Mar (consider as today) and after Jan-31. Here we apply multiple conditions. Select column D13 and apply the formula to find the sum of Qty which satisfies both conditions.

=SUMIFS (D2:D11, C2:C11,”<=”&TODAY (), C2:C11,”>=”&”01/31/2019″)

SUMIFS With Dates Example 1-1

  • Here D2: D1 is the range of Qty shipped C2: C1 is the range of dates shipped. Today () is the function to get the current day that is 18-3-2019. Logical operators are concatenated using the “&” symbol with the function or date.
  • The first criteria will be “<=” &Today (). This will check the given dates with 18- Mar. Since this satisfies an entire column in the date, the Qty will be selected below to find the sum.

SUMIFS With Dates Example 1-3

  • For the second criteria,”>=”&”01/31/2019″, the date greater than or equal to 31-Jan the Qty shipped will select and make the sum as below.

SUMIFS With Dates Example 1-2

  • The result will be the sum of Qty, which comes under both; it will be from the shipped date 1-31-2019 to 3-18-2019. And the sum is 1755, as shown in the 1st screenshot.

SUMIFS with Dates – Example #2

Another set of data is given below. Order number, date of delivery, and Bill Amount are given. We need to find the Bill amount after 3-25-2016 and before 12-25-2016

SUMIFS With Dates Data 2

  • Here we need to find the bill amount between the two mentioned dates. Select F3 and apply the formula

=SUMIFS (D2:D12, C2:C12,”>”&”3/25/2016”, C2:C12,”<“&”12/25/2016”)

SUMIFS With Dates Example 2-5

  • D2: D12 is the sum_range, C2: C12 is the criteria range. The two different criteria are given as ‘>3/25/2016’ and ‘<12/25/2016’.
  • According to the criteria given, sum the bill amount after ‘3/25/2016’ for the first criteria given.

Example 2-2

  • And for the second criteria before ‘12/25/2016’, the amounts will be selected as below.

Example 2-4

  • So when both the criteria are satisfied, the amount we will get is shown in the 1st screenshot. That is after 25-Mar and before 25-Dec. And the sum will be 3583.95.

SUMIFS with Dates – Example #3

In this example, we can use SUMIFS with blank and non-blank criteria.

  • Order date and the delivery date of some products are given with Qty.

Data 3

  • Need to find the Qty of product which is not delivered yet. Applied the formula in cell D13.

=SUMIFS (E2:E11, C2:C11,”<>”, D2:D11,”=”)

Example 3-1

  • In the first condition, ‘C2: C11,”<>”’ we will select the non-empty cells in order date. And in the second condition, we will select the blank cells in the delivery.
  • So both criteria satisfying cells will be as below.

 Example 3-2

  • So the sum of both criteria satisfying cells will be 188 given in D13.

Things to Remember About SUMIFS with Dates

  • The SUMIFs formula returns an error value ‘#VALUE!’ when the criteria do not match with the criteria range.
  • Use the ‘&’ symbol to concatenate an excel function with a criteria string.
  • SUMIFS function works according to the AND logic, which means the range will be summed only if it meets all the given conditions.
  • Whenever you enter an array formula that means a long formula, press Ctrl + Shift + Enter, enclosing your formula within a curly brace that will help you manage the long formula easily.
  • The answer will be ‘0’ when the criteria do not match with each other.

Recommended Articles

This is a guide to SUMIFS with Dates in Excel. Here we discuss how to use SUMIFS Function with Dates in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles–

  1. SUMIF with Multiple Criteria
  2. SUMIF in Excel
  3. Sumif Between Two Dates
  4. Excel SUMIF with OR
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel for HR Training (8 Courses, 10+ Projects)4.9
3 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Excel Course

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

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

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

EDUCBA Login

Forgot Password?

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

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

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

EDUCBA

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

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

Let’s Get Started

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

EDUCBA

Download SUMIFS with Dates Excel Template

EDUCBA

Download SUMIFS with Dates Excel Template

EDUCBA

डाउनलोड SUMIFS with Dates Excel Template

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