How to SUMIF between Two Dates and with Another Criteria (7 Ways)

If you are looking for some of the easiest ways to SUMIF between two dates and another criteria, then you will find this article useful. Adding up values within a limit of time periods and based on criteria is needed to be done sometimes and to do this task quickly you can follow this article.

Download Workbook


7 Ways to SUMIF between Two Dates and with Another Criteria

Here, we have the following dataset containing sales records of some products with their estimated delivery dates and selling regions of a company. Using this dataset we will demonstrate the ways of summing up sales values based on a specified region and date range.

SUMIF between two dates and another criteria

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


Method-1: Using SUMIFS Function to SUMIF between Two Dates with Another Criteria

We want to sum up the sales values for the East Region and for the dates between 1/10/2022 and 3/20/2022 (m-dd-yyyy) by using the SUMIFS function here.

SUMIF between two dates and another criteria

Steps:
➤ Type the following formula in cell E14.

=SUMIFS(E4:E11,C4:C11,">="&B14,C4:C11,"<="&C14,D4:D11,"East")

Here, E4:E11 is the sales range which values we want to sum, C4:C11 is the date range for the first criteria, “>=”&B14 is the first criteria which means greater than or equal to the start date 1/10/2022. The second criteria range is similar to the first one and the criteria for this range is “<=”&C14 which means less than or equal to the end date 3/20/2022 and the last criteria range is D4:D11 containing the regions, the criteria for this range would be East.

SUMIFS function

➤ Press ENTER.
Now, you will get the sum of sales of $13,806.00 for our defined date range with another criterion: East Region.

SUMIFS function

Read More: How to Do SUMIF Date Range Month in Excel (9 Ways)


Method-2: Using SUMIFS and EOMONTH to SUMIF between Two Dates with Another Criteria

In this section, we will try to find the sum of the sales values for the dates of January month and the South Region. So, we will use the EOMONTH function with the SUMIFS function here.

SUMIF between two dates and another criteria

Steps:
➤ Type the following formula in cell D14.

=SUMIFS(E4:E11,C4:C11,">="&B14,C4:C11,"<="&EOMONTH(B14,0),D4:D11,C14)

Here, E4:E11 is the sales range which values we want to sum, C4:C11 is the date range for the first criteria, “>=”&B14 is the first criteria which means greater than or equal to the start date 1/1/2022. The second criteria range is similar to the first one and the criteria for this range is “<=”&EOMONTH(B14,0) which means less than or equal to the end date of January month, 1/31/2022, and the last criteria range is D4:D11 containing the regions, the criteria for this range would be East.

SUMIFS & EOMONTH functions

➤ Press ENTER.
After that, you will get the sum of sales, $6,835.00 for the dates of January month with another criterion: South Region.

SUMIFS & EOMONTH functions

Read More: How to Use SUMIFS to SUM Values in Date Range in Excel


Method-3: SUMIFS and DATE Functions to SUMIF between Two Dates

Here, we will be using the SUMIFS function and the DATE function, to sum up, the sales values for the North Region and for the dates within 1/10/2022 and 3/20/2022.

SUMIF between two dates and another criteria

Steps:
➤ Type the following formula in cell E14.

=SUMIFS(E4:E11,C4:C11,">="&DATE(2022,1,10),C4:C11,"<="&DATE(2022,3,20),D4:D11,D14)

Here, E4:E11 is the sales range which values we want to sum, C4:C11 is the date range for the first and second criteria, and the last criteria range is D4:D11 containing the regions.

  • DATE(2022,1,10)returns a number of a date value
    Output → 44571
  • ">="&DATE(2022,1,10) becomes
    ">= 44571"
  • DATE(2022,3,20)returns a number of a date value
    Output → 44640
  • "<="&DATE(2022,3,20) becomes
    "<= 44640"
  • SUMIFS(E4:E11,C4:C11,">="&DATE(2022,1,10),C4:C11,"<="&DATE(2022,3,20),D4:D11,D14) becomes
    SUMIFS(E4:E11,C4:C11,">= 44571",C4:C11,"<= 44640",D4:D11, “North”)checks if the date values of the range C4:C11 are greater than or equal to 44571 and less than or equal to 44640 and the region North in the D4:D11 range
    Output → $9,496.00

SUMIFS & DATE functions

➤ Press ENTER.
Then, you will get the sum of sales, $9,496.00 for our defined date range with the other criteria: North Region.

SUMIFS & DATE functions

Related Content: How to Use IF Formula for Date Range in Excel (6 Methods)


Similar Readings:


Method-4: Using SUMIFS Function with TODAY

Suppose, you want to get the total sales values for the dates between 1/1/2022 and today’s date (3/23/2022) and for the East Region. And, to do this you can use the TODAY function along with the SUMIFS function.

SUMIF between two dates and another criteria

Steps:
➤ Type the following formula in cell D14.

=SUMIFS(E4:E11,C4:C11,">="&B14,C4:C11,"<="&TODAY(),D4:D11,C14)

Here, E4:E11 is the sales range which values we want to sum, C4:C11 is the date range for the first and second criteria, and the last criteria range is D4:D11 containing the regions.

  • ">="&B14 becomes
    ">= 44562"
  • TODAY()returns today’s date
    Output → 44643 (3/23/2022)
  • "<="&TODAY() becomes
    "<= 44643"
  • SUMIFS(E4:E11,C4:C11,">="&DATE(2022,1,10),C4:C11,"<="&DATE(2022,3,20),D4:D11,D14) becomes
    SUMIFS(E4:E11,C4:C11,">= 44562",C4:C11,"<= 44643",D4:D11, “East”)checks if the date values of the range C4:C11 are greater than or equal to 44562 and less than or equal to 44643 and the region East in the D4:D11 range
    Output → $15,208.00

SUMIFS & TODAY functions

➤ Press ENTER.
Finally, you will get the sum of sales which is $15,208.00 for the dates between the first day of January 2022 and today’s date with criteria: East Region.

SUMIFS & TODAY functions

If you want to change the last date of the date range from today’s date to 10 days prior to today’s date then use the following formula

=SUMIFS(E4:E11,C4:C11,">="&B14,C4:C11,"<="&TODAY()-10,D4:D11,C14)

SUMIFS & TODAY functions

For the last date as a date 10 days following today’s date

=SUMIFS(E4:E11,C4:C11,">="&B14,C4:C11,"<="&TODAY()+10,D4:D11,C14)

SUMIF between two dates and another criteria

Read More: Excel VBA: Filter Date before Today (With Quick Steps)


Method-5: Combination of SUM and IF Functions to SUMIF between Two Dates and with Another Criteria

You can use the combination of the SUM function and the IF function to calculate the total sales for the dates between 1/10/2022 to 3/20/2022 and for the East Region.

SUMIF between two dates and another criteria

Steps:
➤ Type the following formula in cell E14.

=SUM(IF((C4:C11)>=B14,IF((C4:C11)<=C14,IF(D4:D11=D14,E4:E11))))

Here, E4:E11 is the sales range which values we want to sum, C4:C11 is the date range for the first and second criteria, and the last criteria range is D4:D11 containing the regions.

  • IF((C4:C11)>=B14checks if the date values of the range C4:C11 are greater than or equal to the value of B14.
    Output → {FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
  • IF((C4:C11)<=C14checks if the date values of the range C4:C11 are less than or equal to the value of C14.
    Output → {TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE}
  • IF(D4:D11=D14,E4:E11)checks if the regions of the range D4:D11 are equal to the region East of C14 and returns
    ({TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}, E4:E11)
    Output → {1402; 5935; FALSE; 7871; FALSE; FALSE; FALSE; FALSE}
  • IF((C4:C11)>=B14,IF((C4:C11)<=C14,IF(D4:D11=D14,E4:E11))) becomes
    {FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE},{TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE},{1402; 5935; FALSE; 7871; FALSE; FALSE; FALSE; FALSE}
    {FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}, {1402; 5935; FALSE; 7871; FALSE; FALSE; FALSE; FALSE}
    Output → {FALSE; 5935; FALSE; 7871; FALSE; FALSE; FALSE; FALSE}
  • SUM(IF((C4:C11)>=B14,IF((C4:C11)<=C14,IF(D4:D11=D14,E4:E11)))) becomes
    SUM({FALSE; 5935; FALSE; 7871; FALSE; FALSE; FALSE; FALSE})
    Output → $13,806.00

SUM and IF Functions

➤ Press ENTER.
Eventually, you will get the sum of sales, $13,806.00 for our defined date range with other criteria: East Region.

SUM and IF Functions

Related Content: Excel SUMIF with a Date Range in Month & Year (4 Examples)


Method-6: Using SUMPRODUCT, MONTH, and YEAR Functions

Here, we will be using the SUMPRODUCT function, the MONTH function, and the YEAR function to sum up the sales values for the dates of the January month and the East Region.

SUMIF between two dates and another criteria

Steps:
➤ Type the following formula in cell E14.

=SUMPRODUCT((MONTH(C4:C11)=1)*(YEAR(C4:C11)=2022)*(D4:D11=D14)*E4:E11)

Here, E4:E11 is the sales range which values we want to sum, C4:C11 is the date range for the first and second criteria, and the last criteria range is D4:D11 containing the regions.

  • MONTH(C4:C11) → MONTH returns the month number of the dates
    Output → {1;1;1;2;2;3;3;3}
  • MONTH(C4:C11)=1 becomes
    {1;1;1;2;2;3;3;3}=1
    Output → {TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE}
  • YEAR(C4:C11)returns the year values of the dates
    Output → {2022;2022;2022;2022;2022;2022;2022;2022}
  • YEAR(C4:C11)=2022 becomes
    {2022;2022;2022;2022;2022;2022;2022;2022}=2022
    Output → {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
  • D4:D11=D14checks if the regions of the range D4:D11 are equal to the region East of C14
    Output → {TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}
  • SUMPRODUCT((MONTH(C4:C11)=1)*(YEAR(C4:C11)=2022)*(D4:D11=D14)*E4:E11) becomes
    SUMPRODUCT({TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE}*{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}*{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}*E4:E11) 
    SUMPRODUCT({1;1;1;0;0;0;0;0}*{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}*E4:E11)
    SUMPRODUCT({1;1;0;0;0;0;0;0}*E4:E11)  SUMPRODUCT({1402;5935;0;0;0;0;0;0})
    Output → $7,337.00

SUMPRODUCT, MONTH, YEAR Functions

➤ Press ENTER.
Afterward, you will get the sum of sales, $7,337.00 for January month with another criterion: East Region.

SUMPRODUCT, MONTH, YEAR Functions

Read More: Excel SUMIF with a Date Range in Month & Year (4 Examples)


Method-7: VBA Code to SUMIF between Two Dates with Different Criteria

We will use a VBA code here to perform the calculation of the total of the sales values between the two dates 1/10/2022 and 3/20/2022 with a criteria East Region.

SUMIF between two dates and another criteria

Steps:
➤ Go to the Developer Tab >> Visual Basic Option.

VBA Code

Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

SUMIF between two dates and another criteria

After that, a Module will be created.

VBA Code

➤ Write the following code

Sub sumdatewithcriteria()

Cells(14, 5).Value = Application.WorksheetFunction.SumIfs(Range("E4:E11"), _
Range("C4:C11"), ">=" & DateValue("1/10/2022"), Range("C4:C11"), "<=" & _
DateValue("3/20/2022"), Range("D4:D11"), "East")

End Sub

We will get our value in cell E14 and DATEVALUE will convert the date string into a date value and then after fulfilling the criteria SUMIFS will return the added sales value in cell E14.

VBA Code

➤ Press F5.
Finally, you will get the sum of sales of $13,806.00 for our defined date range with another criterion: East Region.

SUMIF between two dates and another criteria

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


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice


Conclusion

In this article, we tried to cover the ways to SUMIF between two dates and another criteria easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

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