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

Working on 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.


Download Practice Book

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


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

Method 1: Use SUMIFS Function to Sum Between Two Dates 

Let’s get introduced to our dataset first. I have placed some salespersons’ names, dates, and sales in my dataset. Now 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.

SUMIFS Function to Sum Between Two Dates

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.

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 result.

👇 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.


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.

👇 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 Jointly 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.

👇 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.

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.

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.

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.


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

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. 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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo