How to Use SUMIFS for Date Range with Multiple Criteria (7 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Working in Excel, you may often need to sum numbers within specific dates. This article will provide you with 7 quick methods to apply the SUMIFS function with date range and multiple criteria.

So let’s have an overview of the methods we are going to use for serving our purpose with relevant output from the methods.

Overview of SUMIFS Date Range Multiple Criteria


How to Use SUMIFS for Date Range with Multiple Criteria: 7 Quick Methods

Let’s get introduced to our dataset first. I have placed some salespersons’ names, dates, and sales in my dataset. Here, we want to find the total sales over the dates.

Dataset for SUMIFS Date Range Multiple Criteria

So, let’s check the 7 ways to use the SUMIFS function for date range with multiple criteria.


Method 1: Use SUMIFS Function to Sum Between Two Dates 

I’ll use the SUMIFS function to find the total sales between two dates. The SUMIFS function in Excel is used to sum up the cells that meet multiple criteria.

Here, I’ll sum up the sales between the dates 1/10/2020 and 10/10/2020

Steps:

➥ Activate Cell C16

Type the formula given below:

=SUMIFS(D5:D12,C5:C12,">"&C14,C5:C12,"<"&C15)

➥ Then just hit the ENTER button.

SUMIFS Function to Sum Between Two Dates

Now you will notice the expected result.

SUMIFS function


Method 2: Combination of SUMIFS and TODAY Functions to Enter a Date Range with Criteria

In this method, we’ll use the combination of the SUMIFS and TODAY functions to sum up the sales from today to any previous or after the date. The TODAY function returns the current date.

I’ll calculate here from today to the previous 5 days.

Steps:

➥ In Cell C14 type the given formula-

=SUMIFS(D5:D12,C5:C12,"<"&TODAY(),C5:C12,">"&TODAY()-5)

➥ Press the ENTER button.

SUMIFS and TODAY Functions to Enter a Date Range with Criteria

Now you will observe that we have got our results.

SUMIFS and TODAY Function

👇 Formula Breakdown

TODAY()

The TODAY function will extract todays’ date. It will return as-

{11/31/2021}

SUMIFS(D5:D12,C5:C12,”<“&TODAY(),C5:C12,”>”&TODAY()-5)

Then the SUMIFS function will calculate the sum between the date from the TODAY function and the previous 5 days. We subtracted 5 from the today function for that reason. That will result in as-

{15805}

Note: To calculate from today to after 5 days just type +5 in the formula.


Method 3: SUMIFS Function to Sum Between Two Dates with Additional Criteria

We can sum up the sales between two date range with additional criteria too using the SUMIFS function. I’ll find the total sales value for “Bob” between the two dates.

Steps:

➥ Write the formula in Cell C16

=SUMIFS(D5:D12,C5:C12,">"&C14,C5:C12,"<"&C15,B5:B12,"*Bob*")

➥ Click the ENTER button then.

SUMIFS Function to Sum Between Two Dates With Additional Criteria

Then you will spot that the sales value of Bob is calculated.

SUMIFS with Another Criteria


Method 4:  Use SUMIFS and DATE Functions Together to Sum with Multiple Criteria

Here, we’ll use another combination of functions- the SUMIFS function and the DATE function. The DATE function is used to return a serial number that corresponds to a date.

Steps:

➥ Type the formula in Cell C16:

=SUMIFS(D5:D12,C5:C12,">"&DATE(2020,1,10),C5:C12,"<"&DATE(2020,10,10))

➥ Hit the ENTER button.

SUMIFS And DATE Functions Together to Sum With Multiple Criteria

Now you will notice that our expected result is calculated.

SUMIFS and DATE Functions

👇 How Does the Formula Work

The DATE function will return a serial number that corresponds to the given date. DATE(2020,1,10) will return as-{43840} and DATE(2020,10,10) will return as-{44114}.

SUMIFS(D5:D12,C5:C12,”>”&DATE(2020,1,10),C5:C12,”<“&DATE(2020,10,10))

Finally the SUMIFS function will sum up the sales value according to that date range and  that will return as-

{22241}


Method 5: Insert SUMIFS and DATE Functions to Sum in a Specific Year

Here, we’ll use the previous methods’ functions again to sum up the sales for a specific year. I’ll calculate here for the year 2021.

Steps:

➥ Activating Cell C16 type the given formula-

=SUMIFS(D5:D12,C5:C12,">"&DATE(2021,1,1),C5:C12,"<"&DATE(2021,12,31))

➥ Press the ENTER button then.

SUMIFS And DATE Functions Jointly to Sum in a Specific Year

Then you will spot that the specific years’ sales value is summed up.

Sum of a year

👇 How Does the Formula Work

This formula works like the previous method.


Method 6: Combination of SUMIFS and EOMONTH Functions to Sum in a Specific Month

In this method, we’ll use the SUMIFS function and the EOMONTH function to sum up for a specific month. The EOMONTH function calculates the last day of the month after adding a specified number of months to a date. I’ll calculate here for the month “March”.

Step 1:

➥ Write the first date of March in Cell C14

SUMIFS And EOMONTH Functions to Sum in A Specific Month

Step 2:

➥ Then press that cell and click as follows- Home > Number > Arrow icon.

A dialog box named “Format Cells” will open up.

SUMIFS And EOMONTH Functions to Sum in A Specific Month

Step 3:

➥ Then press the Custom option.

➥ Write “mmmm” on the Type bar.

➥ Press Ok.

Then the cell will show the month name.

Format Cells Dialog box for SUMIFS And EOMONTH Functions to Sum in A Specific Month

Step 4:

➥ Type the formula in Cell C15 as given below-

=SUMIFS(D5:D12,C5:C12,">="&C14,C5:C12,"<="&EOMONTH(C14,0))

➥ Press the ENTER button now.

Specific Month

Now you will spot that our operation is done.

Sum of a Month

👇 How Does the Formula Work

EOMONTH(C14,0)

The EOMONTH function will store the date as a sequential serial number so it can be used in the calculation. It will return as-

{43921}

SUMIFS(D5:D12,C5:C12,”>=”&C14,C5:C12,”<=”&EOMONTH(C14,0))

Finally, the SUMIFS function will calculate the sales value according to that date range and that will return as-

{18480}


Method 7: Use SUMIFS Function to Sum Between a Date Range from Another Sheet

In our last method, I will show how to use the SUMIFS function to sum between a date range if the data is given in another sheet.

Please have a look that our data is in “Sheet1” but we will calculate in another sheet.

SUMIFS Function to Sum Between a Date Range From Another Sheet

We’ll calculate in this sheet named “Another Sheet”.

Steps:

➥ In Cell C6 write the given formula:

=SUMIFS(Sheet1!D5:D12,Sheet1!C5:C12,">"&C4,Sheet1!C5:C12,"<"&C5)

➥ Then just hit the ENTER button.

SUMIFS Function to Sum Between a Date Range From Another Sheet

 

Please take a look now our calculation is done.

Result for Another Sheet


Download Practice Book

You can download the free Excel template from here and practice on your own.


Conclusion

I hope all of the methods described above will be good enough to use the SUMIFS function to sum within multiple criteria. Feel free to ask any questions in the comment section and please give me feedback.


Related Article


<< Go Back to SUMIFS with Date Range | Excel SUMIFS Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

2 Comments
  1. This is very helpful. I just have one question. When I try to do a sumifs with date ranges, I only get 0s. I figured out that it must be because I have a lot of data points with the same date. My rows are employees’ voids. I pulled the data for last year. Obviously, a lot of voids happen on a single date. I changed the dates to make them all different (even by just a day), and it works. What do I need to do to use this sumifs function for my purposes. Thank you

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo