Sum by Quarter

Excel Formulas › Sum

All versionsSUMIFSDate boundaries

To total amounts for a fiscal or calendar quarter, fence the dates between the quarter’s first and last day with SUMIFS — the same date-boundary trick as summing by month, just a three-month window.

Quick formula: to total column B for the quarter that starts at E2:
=SUMIFS(B:B, A:A, ">="&E2, A:A, "<="&EOMONTH(E2,2))
EOMONTH(E2, 2) is the end of the month two months after the quarter start — i.e. the last day of the quarter.

Functions used (tap for the full reference guide):

SUMIFSDATE

The example

Dated sales; total Q1 (Jan–Mar).

ABDE
1DateAmountQtr startTotal
21/15$100Jan 1$360
32/20$140
43/05$120
54/10$200

The formula

Q1 total — April’s $200 is excluded:

=SUMIFS(B2:B5, A2:A5, ">="&E2, A2:A5, "<="&EOMONTH(E2,2)) // Jan-Mar: 100+140+120 = 360

How it works

Two date conditions span the three months of the quarter:

  1. A2:A5, ">="&E2 keeps rows on or after the quarter’s first day (Jan 1).
  2. EOMONTH(E2, 2) jumps two months forward and lands on that month’s end — Mar 31 — so the second condition keeps rows on or before the quarter end.
  3. Only Jan–Mar rows pass both, so SUMIFS returns 360.
  4. For Q2 set E2 to Apr 1; the same formula then spans Apr–Jun.

Build the quarter starts automatically. If E2 is Jan 1, the next quarter start is =EDATE(E2, 3) — fill that down for Q1–Q4 labels.

Try it: interactive demo

Live demo

Pick a quarter; see its total.

Total:

Variations

Quarter number from a date

Label each row with its quarter:

=ROUNDUP(MONTH(A2)/3, 0)

Sum by quarter with SUMPRODUCT

No helper column, matches quarter and year:

=SUMPRODUCT((ROUNDUP(MONTH(A2:A5)/3,0)=1)*(YEAR(A2:A5)=2026)*B2:B5)

Fiscal quarters (starting in another month)

Shift the start date: a fiscal year beginning in July uses Jul 1 as Q1.

Pitfalls & errors

Spanning years. Matching the quarter number alone sums that quarter across all years. The date-boundary SUMIFS is year-safe.

EOMONTH offset is 2, not 3. From the quarter’s first month, two months forward is the third month; its end is the quarter end.

Text dates won’t total. Convert text dates to real dates first or the comparisons fail.

Practice workbook

📊
Download the free Sum by Quarter practice workbook
Dated sales with live SUMIFS-by-quarter, the quarter-number and SUMPRODUCT variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I sum by quarter in Excel?
Use SUMIFS with the quarter's first and last day: =SUMIFS(B:B, A:A, ">="&qstart, A:A, "<="&EOMONTH(qstart,2)). EOMONTH(qstart,2) gives the quarter-end date.
How do I get the quarter number from a date?
Use =ROUNDUP(MONTH(A2)/3, 0). January–March return 1, April–June return 2, and so on.
How do I handle fiscal quarters that don't start in January?
Use the fiscal first-of-quarter date as qstart. For a July fiscal year, Q1 starts July 1, and the EOMONTH(qstart,2) boundary still gives the right quarter end.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Sum by month · SUMIFS with multiple criteria

Function references: SUMIFS · DATE