How to Do SUMIF Date Range 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.

Download Workbook

9 Ways to Do SUMIF Date Range Month in Excel

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 versions at your convenience.

SUMIF date range month

SUMIF date range month

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.

SUMIF date range month

Step-01:
For this case, the output cell is C15.
➤Type the following formula in Cell C15

=SUMIFS(D5:D11,C5:C11,">="&DATE(2021,1,1),C5:C11,"<="&DATE(2021,1,31))

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

using SUMIFS function

➤Press ENTER

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

using SUMIFS function

Method-2: Using 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.

SUMIF date range month

Step-01:
➤Select the output Cell D15.
➤Type the following formula

=SUMIFS($D$5:$D$11,$C$5:$C$11,">="&C15,$C$5:$C$11,"<="&EOMONTH(C15,0))

$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 criteria where EOMONTH will return the last date of a month.

using SUMIFS and EOMONTH function

➤Press ENTER
➤Drag down the Fill Handle tool.

using SUMIFS and EOMONTH function

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

SUMIF date range month

Method-3: Using 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.

SUMIF date range month

Step-01:
➤Select the output Cell C16
➤Type the following formula

=SUMPRODUCT((MONTH(C6:C12)=1)*(YEAR(C6:C12)=2021)*(D6:D12))

D6:D12 is the range of Sales, 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

using SUMPRODUCT function

➤Press ENTER

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

using SUMPRODUCT function

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.

SUMIF date range month

Step-01:
➤Select the output Cell C15
➤Type the following formula

=SUMIFS(D5:D11,E5:E11,"East",C5:C11,">="&DATE(2021,1,1),C5:C11,"<="&DATE(2021,1,31))

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 criteria
">="&DATE(2021,1,1) is the second criteria where DATE will return the first date of a month.
"<="&DATE(2021,1,31) is used as the third criteria where DATE will return the last date of a month.

Sum up based on criteria

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

Sum up based on criteria

Method-5: Using SUM and IF function for a 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.

SUMIF date range month

Step-01:
Here, the output cell is C15.
➤Type the following formula in Cell C15
➤Type the following formula

=SUM(IF(MONTH(C5:C11)=1,IF(YEAR(C5:C11)=2021,IF(E5:E11="East",D5:D11))))

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.

using SUM and IF function

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

using SUM and IF function


Further Readings:


Method-6: Using Pivot Table

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

SUMIF date range month

Step-01:
➤Go to Insert Tab>>PivotTable Option

using Pivot Table

Create PivotTable Dialog Box will pop up.

➤Select the table/range
➤Click on New Worksheet
➤Press OK

using Pivot Table

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

SUMIF date range month

Step-02:
➤Drag down Date to the Rows area and Sales to the Values area.

using Pivot Table

After that, the following table will be created.

using Pivot Table

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

using Pivot Table

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

using Pivot Table

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

SUMIF date range month

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.

SUMIF date range month

Case-1: Total Cost for Non-Empty Dates

Step-01:
➤Select the output Cell C12
➤Type the following formula

=SUMIF(D5:D10,"<> ",E5:E10)

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

Empty or Non-Empty Dates

➤Press ENTER

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

Empty or Non-Empty Dates

Case-2: Total Cost for Empty Dates

Step-01:
➤Select the output Cell C13
➤Type the following formula

=SUMIF(D5:D10,"",E5:E10)

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

Empty or Non-Empty Dates

➤Press ENTER

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

SUMIF date range month

Method-8: Using 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.

SUMIF date range month

Step-01:
In this case, the output cell is C15.
➤Type the following formula in Cell C15
➤Type the following formula

=SUMPRODUCT((MONTH(C5:C11)=1)*(D5:D11))

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

same month of different years

➤Press ENTER

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

same month of different years

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.

SUMIF date range month

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

Step-01:
➤Select the output Cell C12
➤Type the following formula

=SUMIFS(E5:E10, D5:D10, "<"&TODAY(), D5:D10, ">="&TODAY()-10)

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.

using TODAY function

➤Press ENTER

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

SUMIF date range month

Case-2: Sum of Costs after 10 days from Today

Step-01:
➤Select the output Cell C13
➤Type the following formula

=SUMIFS(E5:E10,D5:D10, ">"&TODAY(), D5:D10, "<="&TODAY()+10)

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.

using TODAY function

➤Press ENTER

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

using TODAY function

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.

Practice

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.


Further Readings:

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo