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


Download Practice Book

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


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

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

Read more: How to Use SUMIFS to SUM Values in Date Range in Excel


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.

Read More: Exclude Multiple Criteria in Same Column with SUMIFS Function


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

Read More: Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria


Similar Readings


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}

Read More: How to Use SUMIFS Function in Excel with Multiple Criteria


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.

Read More: [Fixed]: SUMIFS Not Working with Multiple Criteria (3 Solutions)


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}

Read More: SUMIFS Sum Range Multiple Columns in Excel(6 Easy Methods)


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

Read More: SUMIFS with Multiple Criteria in the Same Column (5 Ways)


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 Articles

Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

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