“Sales from 1 to 15 October”, “this month’s collections for Delhi or Mumbai”. SUMIFS does AND conditions naturally, and with two small tricks it handles date ranges and OR too.

In this article

1. Between two dates
2. This month and last month without typing dates
3. Dates with times
4. OR: North or East
5. OR across two different columns
6. Excel 365 alternative
7. Where people go wrong
8. Practice

Between two dates

Dates in A2:A201, amounts in I2:I201, start date in L1, end date in L2:

=SUMIFS(I2:I201, A2:A201, ">=" & L1, A2:A201, "<=" & L2)

The operator goes in quotes, then & joins the cell. Writing ">=L1" compares with the text “L1” and returns 0.

This month and last month without typing dates

=SUMIFS(I2:I201, A2:A201, ">=" & EOMONTH(TODAY(), -1) + 1, A2:A201, "<=" & EOMONTH(TODAY(), 0))   ' this month
=SUMIFS(I2:I201, A2:A201, ">=" & EOMONTH(TODAY(), -2) + 1, A2:A201, "<=" & EOMONTH(TODAY(), -1))  ' last month

Dates with times

If column A holds date-times, “<= 15 Oct” stops at midnight and drops the whole of 15 Oct. Use “< next day”:

=SUMIFS(I2:I201, A2:A201, ">=" & L1, A2:A201, "<" & L2 + 1)

OR: North or East

=SUM(SUMIFS(I2:I201, B2:B201, {"North","East"}))

The array constant runs SUMIFS twice and SUM adds the results. It combines freely with dates:

=SUM(SUMIFS(I2:I201, B2:B201, {"North","East"}, A2:A201, ">=" & L1, A2:A201, "<=" & L2))

Regions listed in cells N2:N4 instead? =SUM(SUMIFS(I2:I201, B2:B201, N2:N4)) (Ctrl+Shift+Enter in Excel 2019 and older).

OR across two different columns

“Region is North OR product is Laptop”: the same row can match both, so adding two SUMIFS double-counts. Use SUMPRODUCT with a >0 test:

=SUMPRODUCT(I2:I201 * (((B2:B201="North") + (C2:C201="Laptop")) > 0))

Excel 365 alternative

=SUM(FILTER(I2:I201, (A2:A201>=L1) * (A2:A201<=L2) * ((B2:B201="North") + (B2:B201="East")), 0))

Inside FILTER, * means AND and + means OR.

Where people go wrong

Mistake	Fix
">=L1" all inside quotes	">=" & L1
Dates stored as text (left-aligned)	Convert with Data › Text to Columns › Finish, or DATEVALUE
Adding two SUMIFS for OR on different columns	SUMPRODUCT with (…+…)>0
Typing “1/10/26” in criteria	Day/month is ambiguous; use DATE(2026,10,1) or a cell

Practice

Download the combo practice workbook below. Its Sales sheet of 200 orders (dates, regions, products, customers, amounts) is ready data to try every formula on this page, and the Practice sheet has 50 checked tasks on related combos.

More: all formula combos · Excel function course.

Keep reading

Formula CombosRolling Totals in Excel: Last 7 Days, Last 30 Days and Moving AveragesFormula CombosRank in Excel: Handle Ties and Rank Within Each GroupFormula CombosSplit Text by Comma, Dash or Any Delimiter in Excel (TEXTSPLIT, TEXTBEFORE, TEXTAFTER)