# How to Use the SUMIFS for a Date Range with Multiple Criteria – 7 Methods

This is an overview of the method and the sample dataset.

In this sample dataset you want to find the total sales over the dates.

### Method 1 – Using the SUMIFS Function to calculate a value Between Two Dates

Steps:

• Select C16
• Enter the formula below:
`=SUMIFS(D5:D12,C5:C12,">"&C14,C5:C12,"<"&C15)`
• Press ENTER.

This is the output.

### Method 2 – Combination of the SUMIFS and the TODAY Functions to Enter a Date Range with Criteria

Steps:

• Enter this formula in C14.
`=SUMIFS(D5:D12,C5:C12,"<"&TODAY(),C5:C12,">"&TODAY()-5)`
• Press ENTER.

This is the output.

Formula Breakdown

TODAY() extracts todays’ date. It will return – {11/31/2021}

SUMIFS(D5:D12,C5:C12,”<“&TODAY(),C5:C12,”>”&TODAY()-5) calculates the sum between the date from the TODAY function and the 5 previous days. We subtracted 5 from the today function. That will result in –{15805}

Note: To calculate a sum between today and the 5 following days use +5 in the formula.

### Method 3 – Using the SUMIFS Function to calculate a Sum Between Two Dates with Additional Criteria

Steps:

• Enter the formula in C16.
`=SUMIFS(D5:D12,C5:C12,">"&C14,C5:C12,"<"&C15,B5:B12,"*Bob*")`
• Press ENTER.

The sales value for Bob is calculated.

### Method 4 – Using the SUMIFS and the DATE Functions Together to Sum with Multiple Criteria

Steps:

• Enter the formula in C16:
`=SUMIFS(D5:D12,C5:C12,">"&DATE(2020,1,10),C5:C12,"<"&DATE(2020,10,10))`
• Press ENTER.

This is the output.

Formula Breakdown

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

SUMIFS(D5:D12,C5:C12,”>”&DATE(2020,1,10),C5:C12,”<“&DATE(2020,10,10)) sums up the sales value according to the date range and returns –{22241}

### Method 5 – Inserting the SUMIFS and the DATE Functions to Sum in a Specific Year

Steps:

• Enter this formula in C16.
`=SUMIFS(D5:D12,C5:C12,">"&DATE(2021,1,1),C5:C12,"<"&DATE(2021,12,31))`
• Press ENTER.

This is the output.

### Method 6 – Combining the SUMIFS and EOMONTH Functions to Sum in a Specific Month

Step 1:

• Enter the first date of March in C14

Step 2:

• Select that cell and click Home > Number > Arrow icon.

The dialog box “Format Cells” will open.

Step 3:

• Choose Custom.
• Enter “mmmm” in Type.
• Click Ok.

The cell will show the name of the month.

Step 4:

• Enter this formula in C15.
`=SUMIFS(D5:D12,C5:C12,">="&C14,C5:C12,"<="&EOMONTH(C14,0))`
• Press ENTER.

This is the output.

Formula Breakdown

EOMONTH(C14,0) stores the date as a sequential serial number so it can be used in the calculation. It will return {43921}

SUMIFS(D5:D12,C5:C12,”>=”&C14,C5:C12,”<=”&EOMONTH(C14,0)) calculates the sales value according to the date range and returns {18480}

### Method 7- Using the SUMIFS Function to calculate a Sum Between a Date Range from Another Sheet

This is “Sheet1”.

Another Sheet” will be used to calculate.

Steps:

• Enter this formula in C6 .
`=SUMIFS(Sheet1!D5:D12,Sheet1!C5:C12,">"&C4,Sheet1!C5:C12,"<"&C5)`
• Press ENTER.

This is the output.

## Related Article

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

Get FREE Advanced Excel Exercises with Solutions!
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