SUMIFS Between Two Dates (and Other Multiple-Criteria Examples)

What you will build

Brightline Trading Co. has a list of 20 sales invoices for September and October 2026. We want the sales from Sep 15 to Oct 14, 2026, a billing period that does not line up with calendar months, and then a few variations of the same question.

SUMIFS and COUNTIFS work in Excel 2010 and later, Microsoft 365 and Excel for the web. If you only need calendar-month totals, summing monthly values with SUMIFS covers that layout; this post is about any date range.

How SUMIFS handles a date range

SUMIFS adds up a column where every condition is true. The arguments come in pairs after the sum range:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

A date range is simply two conditions on the same date column: the date is on or after the start, and on or before the end. Because both must be true, SUMIFS only adds the rows in between. The conditions are written as text, with the comparison operator in quotes, joined to the cell with &:

Step 1: Set up the data and the date criteria

1. Put the sales list in A3:E23 with the headers Date, Invoice #, Customer, Region and Amount, one invoice per row.
2. In G3:H5 type the criteria: Start date 9/15/2026, End date 10/14/2026, Region East.
3. In E24 enter =SUM(E4:E23). The whole list totals $47,955.00.

Step 2: Sum between two dates

In G8 type Sales between the dates, then click H8 and enter:

=SUMIFS($E$4:$E$23,$A$4:$A$23,">="&$H$3,$A$4:$A$23,"<="&$H$4)

• $E$4:$E$23 is what to add up: the amounts.
• $A$4:$A$23,">="&$H$3 keeps dates on or after Sep 15, 2026.
• $A$4:$A$23,"<="&$H$4 keeps dates on or before Oct 14, 2026.

Step 3: Add a third criterion (and a NOT criterion)

Each extra condition is one more range/criteria pair. For East only, in H10:

=SUMIFS($E$4:$E$23,$A$4:$A$23,">="&$H$3,$A$4:$A$23,"<="&$H$4,$D$4:$D$23,$H$5)

For every region except East, change the last criterion to "<>"&$H$5 in H11.

Typing the region by hand invites spelling mistakes. A drop-down list in H5 (Data > Data Validation > List) keeps it consistent, and a dependent drop-down list can narrow customers by region.

Step 4: OR logic: East or West

SUMIFS joins its conditions with AND. If you add $D$4:$D$23,"East",$D$4:$D$23,"West", you are asking for invoices that are in East and West at the same time, so the answer is 0. To get East or West, give SUMIFS a list of regions and add up the results:

=SUMPRODUCT(SUMIFS($E$4:$E$23,$A$4:$A$23,">="&$H$3,$A$4:$A$23,"<="&$H$4,$D$4:$D$23,{"East","West"}))

SUMIFS returns two totals, one for East and one for West, and SUMPRODUCT adds them. In Microsoft 365 you can use SUM instead of SUMPRODUCT; SUMPRODUCT works in every version without pressing Ctrl+Shift+Enter.

Step 5: Whole months and weekly totals

The start and end dates do not have to be typed. Two common patterns:

1. The whole month of a date. EOMONTH returns the last day of a month, so EOMONTH($H$4,-1)+1 is the first day of the end date's month and EOMONTH($H$4,0) is the last day. In H13:
   Copy

   =SUMIFS($E$4:$E$23,$A$4:$A$23,">="&EOMONTH($H$4,-1)+1,$A$4:$A$23,"<="&EOMONTH($H$4,0))

2. Weekly totals. In G18 enter the first week start, 9/14/2026 (a Monday), and in G19 =G18+7, copied down to G22. In H18 enter the formula below and copy it down:
   Copy

   =SUMIFS($E$4:$E$23,$A$4:$A$23,">="&G18,$A$4:$A$23,"<"&G18+7)

The weekly formula uses "<"&G18+7, strictly less than the next start date, rather than "<="&G18+6. Both give the same answer for plain dates, but the strict version also works when the dates carry times, and it makes it impossible for a boundary date to land in two weeks. October from EOMONTH is $23,480.00.

Step 6: Verify the total

A second formula that works in a completely different way is the best check. In H14:

=SUMPRODUCT(($A$4:$A$23>=$H$3)*($A$4:$A$23<=$H$4)*$E$4:$E$23)

Each comparison returns TRUE or FALSE for every row; multiplying them turns TRUE into 1 and FALSE into 0, so only amounts inside the range survive. In H15, =IF(AND(ROUND(H8-H14,2)=0,ROUND(H10+H11-H8,2)=0),"Ties","Check") confirms that SUMPRODUCT agrees with SUMIFS and that East plus not-East equals the total.

Then check the boundaries by eye. INV-2105 on Sep 15 and INV-2115 on Oct 14 must be included; INV-2104 on Sep 14 and INV-2116 on Oct 15 must not. Boundary dates are where date-range formulas usually go wrong.

Where you will use this

Date-range SUMIFS is behind a lot of accounting work. The monthly profit and loss statement pulls each month from a transaction list with SUMIFS. A bank reconciliation often needs the deposits recorded between two statement dates. And in an accounts receivable aging report, the same technique totals the invoices issued in a period. To see the matching rows instead of just their total, the FILTER function takes the same conditions; to count overdue items instead, see COUNTIFS for overdue items.

Common mistakes and quick fixes

Frequently asked questions

Final checklist

• Dates in the list are real dates, and the start and end dates are in their own cells.
• Operators are in quotes and joined to the cell with &.
• All ranges cover the same rows and are locked with dollar signs.
• OR conditions use an array constant inside SUMPRODUCT, not two criteria on the same column.
• Dates with times use “<“&end+1.
• A SUMPRODUCT check agrees, and the boundary dates are included or excluded as intended.

Once you can total any date range, building period reports is mostly layout. The monthly P&L and the monthly SUMIFS summary are good next steps.