Pinterest Pixel

Harnessing the Power of Excel SUMIFS with Date Range – 2 Useful Examples

The SUMIFS function allows you to sum values in a range that satisfies multiple conditions. When it comes... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Harnessing the Power of Excel SUMIFS with Date Range - 2 Useful Examples

The SUMIFS function allows you to sum values in a range that satisfies multiple conditions. When it comes to working with dates, SUMIFS with date range can be particularly handy, especially when you need to sum values within a specific date range.

In this article, we will explore the following topics in detail –

Let us look at each of these topics.

Download the Excel Workbook below to follow along and understand how to use SUMIFS with date range in Excel –

download excel workbookSUMIFS-with-date-range.xlsx

 

Introduction to SUMIFS with date range

SUMIFS allows you to sum values in a range that meets multiple conditions or criteria. Whether you’re analyzing sales data, tracking project milestones, or monitoring expenses over time, SUMIFS can simplify your data calculations.

Its syntax is as follows:

=SUMIFS(Sum_Range,Criteria_Range1,Criteria1,Criteria_Range2,Criteria2…)

  • Sum_Range (required) – The range of cells to sum.
  • Criteria_Range1 (required) – The range that is tested using Criteria1. Criteria_range1 and Criteria1 set up a search pair whereby a range is searched for specific criteria. Once items in the range are found, their corresponding values in Sum_range are added.
  • Criteria1 (required) – The criteria that define which cells in Criteria_range1 will be added. For example, criteria can be entered as 32, “>32”, B4, “apples”, or “32”.
  • Criteria_Range2, Criteria2, … (optional) – Additional ranges and their associated criteria.

For SUMIFS with date range, we need to provide the start date and the end date as the two criteria. Let us look at a few examples to help us understand this function better.

 

Basic Example

Suppose you have dates in one column and the sales amount in another column, and you want to calculate the total sales for the month of January. This calculation can easily be done with the help of SUMIFS with date range.

STEP 1: Enter the SUMIFS function.

=SUMIFS(

Harnessing the Power of Excel SUMIFS with Date Range - 2 Useful Examples

STEP 2: Enter the first argument – Sum_range. Here we have selected the range B2:B30 as it contains all the sales figures.

=SUMIFS(B2:B30,

Harnessing the Power of Excel SUMIFS with Date Range - 2 Useful Examples

STEP 3: Enter the second argument – Criteria_range1. Here we have selected A2:A30 range as it contains the date of the sales transaction.

=SUMIFS(B2:B30,A2:A30,

Harnessing the Power of Excel SUMIFS with Date Range - 2 Useful Examples

STEP 4: Enter the third argument – Criteria1. Here we have entered the criteria as “>=”&1/1/2023.

=SUMIFS(B2:B30,A2:A30,”>=”&”1/1/2023″,

Harnessing the Power of Excel SUMIFS with Date Range - 2 Useful Examples

STEP 5: Enter the fourth argument – Criteria_range2. Here we have selected A2:A30 range as it contains the date of the sales transaction.

=SUMIFS(B2:B30,A2:A30,”>=”&”1/1/2023″,A2:A30,

Harnessing the Power of Excel SUMIFS with Date Range - 2 Useful Examples

STEP 6: Enter the fifth argument – Criteria2. Here we have entered “<=”&1/31/2023.

=SUMIFS(B2:B30,A2:A30,”>=”&”1/1/2023″,A2:A30,”<=”&”1/31/2023″)

Harnessing the Power of Excel SUMIFS with Date Range - 2 Useful Examples

As we can see, the SUMIFS function returns the sum of sales for the month of January, applying multiple criteria at once.

Harnessing the Power of Excel SUMIFS with Date Range - 2 Useful Examples

 

SUMIFS with dynamic date range

In many cases, you would want to create dynamic date ranges that automatically adjust based on the current date or other factors. This allows you to analyze data for the current month, quarter, or any other time frame without manually updating your formulas.

This can be achieved by the help of the formula – TODAY(). TODAY() function returns the current date and gets updated automatically each day, reflecting the current date.

Suppose you want to calculate the sum of sales for the last 7 days using the SUMIFS function. Follow the steps below to get the result –

STEP 1: Enter the SUMIFS function.

=SUMIFS(

Harnessing the Power of Excel SUMIFS with Date Range - 2 Useful Examples

STEP 2: Enter the first argument – Sum_range. Here we have selected the range B2:B30 as it contains all the sales figures.

=SUMIFS(B2:B30,

Harnessing the Power of Excel SUMIFS with Date Range - 2 Useful Examples

STEP 3: Enter the second argument – Criteria_range1. Here we have selected A2:A30 range as it contains the date of the sales transaction.

=SUMIFS(B2:B30,A2:A30,

Harnessing the Power of Excel SUMIFS with Date Range - 2 Useful Examples

STEP 4: Enter the third argument – Criteria1. Here we have entered the criteria as greater than 7 days minus today’s date i.e. “>”&TODAY()-7

=SUMIFS(B2:B30,A2:A30,”>”&TODAY()-7,

Harnessing the Power of Excel SUMIFS with Date Range - 2 Useful Examples

STEP 5: Enter the fourth argument – Criteria_range2. Here we have selected A2:A30 range as it contains the date of the sales transaction.

=SUMIFS(B2:B30,A2:A30,”>”&TODAY()-7,A2:A30,

Harnessing the Power of Excel SUMIFS with Date Range - 2 Useful Examples

STEP 6: Enter the fifth argument – Criteria2. Here we have entered the “>=”&TODAY() function.

=SUMIFS(B2:B30,A2:A30,”>”&TODAY()-7,A2:A30,”<=”&TODAY())

Harnessing the Power of Excel SUMIFS with Date Range - 2 Useful Examples

As we can see, the SUMIFS function returns the sum of sales for the last 7 days.

Harnessing the Power of Excel SUMIFS with Date Range - 2 Useful Examples

 

Conclusion

Mastering SUMIFS with date range in Excel opens up a world of possibilities for efficient data analysis. Whether you need to calculate totals for specific months or create dynamic date ranges that adapt to changing time frames, the SUMIFS function is a powerful tool in Excel.

Further learning:

Click here to learn all about SUMIFS in Excel.

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!