How to Use SUMIF in Date Range and Month in Excel (9 Ways)

If you are looking for some of the easiest ways to do SUMIF date range month, then you are in the right place. For calculating records of sales or expenditures or these types of calculations based on a month for different dates, Excel is very helpful.

So, let’s dive into the article to know the ways of summing up values for a date range of a month. Also, here is an overview of the final output that we will get.

SUMIF Date Range Month: 9 Ways

I have the following two data tables; one is the Record of Sales of a company and the other is for a construction company containing different projects and their costs.
Using these data tables I will explain the ways to do SUMIF date range month in Excel. Here, the date format is mm-dd-yyyy.
For this purpose, I am using Microsoft Excel 365 version, but you can use any other version at your convenience.

Method-1: Using SUMIFS function for a Date Range of a Month

If you want to add the sales for a date range of January month then you can use the SUMIFS function and the DATE function.

Step-01:

• For this case, the output cell is C15.
• Type the following formula in Cell C15:

D5:D11 is the range of Sales, and C5:C11 is the criteria range which includes the Dates.
">="&DATE(2021,1,1) is the first criterion where DATE will return the first date of a month.
"<="&DATE(2021,1,31) is used as the second criterion where DATE will return the last date of a month.

• Press ENTER.

Result:
Now, you will get the sum of sales for a date range of 9-Jan to 27-Jan.

Method-2: Combining SUMIFS function and EOMONTH function

For adding the sales for different date ranges of different months, you can use the SUMIFS function and the EOMONTH function. Here, I will get the total Sales value for different date ranges of January and February month.

Step-01:

• Select the output Cell D15.
• Type the following formula:

$D$5:$D$11 is the range of Sales, $C$5:$C$11 is the criteria range
">="&C15 is the first criteria, where C15 is the first date of a month.
"<="&EOMONTH(C15,0) is used as the second criterion where EOMONTH will return the last date of a month.

• Press ENTER.
• Drag down the Fill Handle tool.

Result:
Then, you will get the sum of sales for different date ranges of January and February.

Read More: Sum Values Based on Date in Excel

Method-3: Applying SUMPRODUCT function

You can add the sales for a date range of January month by using the SUMPRODUCT function, the MONTH function, and the YEAR function.

Step-01:

• Select the output Cell C16.
• Type the following formula:

D6:D12 is the range of Sales, and C6:C12 is the range of Dates.

MONTH(C6:C12)will return the months of the dates, and then it will be equal to 1 and it means January.
YEAR(C6:C12)will provide the years of the dates and then it will be equal to 2021.

• Press ENTER.

Result:
Then, you will get the sum of sales for a date range of 9-Jan to 27-Jan.

Method-4: Summing up Values for a Date Range of a Month based on Criteria

Let’s say, you want to sum up the Sales of a date range for January month for the East Region. You can do this by using the SUMIFS function and the DATE function.

Step-01:

• Select the output Cell C15.
• Type the following formula:

D5:D11 is the range of Sales, E5:E11 is the first criteria range and C5:C11 is the second and third criteria range.
East is used as the first criterion.

">="&DATE(2021,1,1) is the second criterion where DATE will return the first date of a month.
"<="&DATE(2021,1,31) is used as the third criterion where DATE will return the last date of a month.

• Press ENTER.

Result:
Afterward, you will get the sum of sales for a date range of 9-Jan to 27-Jan for the East Region.

Method-5: Combining SUM and IF Function for Date Range of a Month Based on Criteria

If you want to sum up the Sales of a date range for January month for the East Region, then you can do this by using the SUM function and the IF function.

Step-01:
Here, the output cell is C15.

• Type the following formula in Cell C15.
• Next, type the following formula:

For the IF function, three logical conditions have been used here which will match up the desired date range and the criteria for the East Region.

• Press ENTER.

Result:
Then, you will get the sum of sales for a date range of 9-Jan to 27-Jan for the East Region.

Method-6: Utilizing Excel Pivot Table

You can have the sum of sales for a date range of a month using the Pivot Table.

Step-01:

• Go to Insert Tab>>PivotTable option.

Create PivotTable Dialog Box will pop up.

• Select the table/range.
• Click on New Worksheet.
• Press OK.

Then a new sheet will appear where you have two portions named PivotTable1 and PivotTable Fields.

Step-02:

• Drag down the Date to the Rows area and Sales to the Values area.

After that, the following table will be created.

• Select any Cell of the Row Labels column.
• Right-click on your mouse.
• Choose Group Option.

• Click on the Days and Months option in the indicated area.
• Press OK.

Result:
Then, you will get the sum of sales for a range of dates of a month as below.

Method-7: Using SUMIF Function Based on Empty or Non-Empty Dates

If you want to get the sum of Costs for the deadline of the projects which are Empty or Non-Empty, then you can use the SUMIF function.

Case-1: Total Cost for Non-Empty Dates

Step-01:

• Select the output Cell C12.
• Type the following formula:

E5:E10 will give the range of Sales.
D5:D10 is the range of Dates.
“<> ” means not equal to Blank.

• Press ENTER.

Result:
Now, you will get the Total Cost for Non-Empty Dates.

Case-2: Total Cost for Empty Dates

Step-01:

• Select the output Cell C13.
• Type the following formula:

E5:E10 will give the range of Sales.
D5:D10 is the range of Dates.
“” means equal to Blank.

• Press ENTER.

Result:
Afterward, you will get the Total Cost for Empty Dates.

Method-8: Use of SUMPRODUCT Function for Same Month of Different Years

For having the sum of Sales for a month irrespective of years, you can use the SUMPRODUCT function.

Step-01:
In this case, the output cell is C15.

• Type the following formula in Cell C15.
• Type the following formula:

D5:D11 will give the range of Sales.
MONTH(C5:C11)=1 is for January month.

• Press ENTER.

Result:
In this way, you will get the sum of Sales for January month of different years.

Read More: How to Do SUMIF by Month and Year in Excel

Method-9: Using TODAY Function to Sum Up Values

If you want to get the sum of Costs for the deadline of the projects which are 10 days before Today or 10 days after Today, then you can use the SUMIFS function and the TODAY function.

Case-1: Sum of Costs Before 10 days from Today

Step-01:

• Select the output Cell C12.
• Type the following formula:

TODAY() will give today’s date.
"<"&TODAY() is the first criteria and the second criteria is “>=”&TODAY()-10.
E5:E10 will give the range of Sales.
D5:D10 is the range of Dates.

• Press ENTER.

Result:
Now, you will get the Sum of Costs before 10 days.

Case-2: Sum of Costs After 10 Days from Today

Step-01:

• Select the output Cell C13.
• Type the following formula:

TODAY() will give today’s date.
">"&TODAY() is the first criterion and the second criterion is “<=”&TODAY()+10.
E5:E10 will give the range of Sales.
D5:D10 is the range of Dates.

• Press ENTER.

Result:
Afterward, you will get the Sum of Costs after 10 days.

Practice Section

For doing practice by yourself we have provided a Practice section like below for each method in each sheet on the right side. Please do it by yourself.

Download Practice Workbook

Conclusion

In this article, I tried to cover the easiest ways to do SUMIF date range month in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.

<< Go Back to SUMIF Date Range | Excel SUMIF Function | Excel Functions | Learn Excel