# 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. ## 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. 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. Now you will notice the expected result. ### 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. Now you will observe that we have got our results. 👇 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. Then you will spot that the sales value of Bob is calculated. ### 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. Now you will notice that our expected result is calculated. 👇 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. Then you will spot that the specific years’ sales value is summed up. 👇 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 Step 2:

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

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

➥ Then press the Custom option.

➥ Write “mmmm” on the Type bar.

➥ Press Ok.

Then the cell will show the month name. 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. Now you will spot that our operation is done. 👇 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. 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. Please take a look now our calculation is done. ## 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

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.

1. Reply 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

• Reply Greetings, Nat Troy. We don’t have a large enough dataset to test your problem. Please send us your file to [email protected]. So we can take a closer look at the issue. Advanced Excel Exercises with Solutions PDF  