How to Sum by Quarter in Excel

To sum by quarter in Excel, you add up every amount whose date falls between the first and last day of that quarter. SUMIFS with two date conditions does this in one formula, and SUMPRODUCT can sum by the quarter number directly. This guide shows both, builds a Q1 to Q4 summary table, and covers fiscal quarters that don't start in January. It is for anyone who reports sales, costs or hours by quarter.

All examples use this list of 2026 sales:

What is a quarter?#

A calendar quarter is a block of three months. Q1 is January to March, Q2 is April to June, Q3 is July to September and Q4 is October to December. Excel stores dates as numbers, so "between two dates" is just "between two numbers". That is what makes SUMIFS work. If dates still feel strange, practise the date basics first.

To get the quarter number from one date, use:

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

May is month 5, 5 / 3 is 1.67, and rounding up gives 2. The guide to getting the quarter from a date covers labels like "Q2 2026" and more.

How to sum a quarter with SUMIFS#

SUMIFS adds the amounts that meet every condition. For a quarter, give it two conditions on the same date column: on or after the first day, and before the first day of the next quarter.

=SUMIFS(B2:B9,A2:A9,">="&DATE(2026,4,1),A2:A9,"<"&DATE(2026,7,1))

The result is 9,500 (5,000 + 2,600 + 1,900). The & joins the operator to the date, and DATE builds the date from year, month and day, so it works in every regional date format. Using "before the next quarter" instead of "on or before 30 June" also catches dates that include a time of day.

The SUMIFS function guide covers the syntax, and the guide to summing between two dates shows the same pattern for any range. Practise dates in criteria to get the quotes and & right every time.

How to build a Q1 to Q4 summary table#

For a full summary, put each quarter's start date in a cell and let one formula do the rest. In D2:D5, type the first day of each quarter: 1 Jan 2026, 1 Apr 2026, 1 Jul 2026 and 1 Oct 2026. Then in E2:

=SUMIFS($B$2:$B$9,$A$2:$A$9,">="&D2,$A$2:$A$9,"<"&EDATE(D2,3))

EDATE(D2,3) returns the date three months later, which is the start of the next quarter. Fill the formula down to E5:

The four totals add up to 27,700, the same as the whole column, which is a quick check that no row was missed. The $ signs keep the data ranges fixed while D2 moves down. To show "Q1 2026" instead of a date, use a custom number format or a label column. To get quick at tables like this, practise SUMIFS with more than one condition.

How to sum by quarter with SUMPRODUCT#

SUMPRODUCT can turn each date into a quarter number and add the matching amounts, with no helper column:

=SUMPRODUCT((ROUNDUP(MONTH(A2:A9)/3,0)=2)*B2:B9)

For each row, the test gives TRUE or FALSE. Multiplying by the amounts turns TRUE into 1 and FALSE into 0, so only Q2 amounts are added. The result is again 9,500.

This formula ignores the year. If your data covers 2025 and 2026, April 2025 and April 2026 are both "quarter 2". Add a year test:

=SUMPRODUCT((ROUNDUP(MONTH(A2:A9)/3,0)=2)*(YEAR(A2:A9)=2026)*B2:B9)

SUMPRODUCT works in every version of Excel. The SUMPRODUCT function guide explains how it multiplies and adds arrays.

How to sum by fiscal quarter#

A fiscal quarter follows your company's financial year, which may start in a month other than January. For a year that starts in April, April to June is fiscal Q1 and January to March is fiscal Q4. Shift the month first:

=INT(MOD(MONTH(A2)-4,12)/3)+1

MOD(MONTH(A2)-4,12) counts months from April, so April is 0 and March is 11. Dividing by 3 and adding 1 gives the fiscal quarter. To total fiscal Q1 in one step:

=SUMPRODUCT((INT(MOD(MONTH(A2:A9)-4,12)/3)+1=1)*B2:B9)

On the sample data, fiscal Q1 is April to June, so the result is 9,500. Change the 4 to the month your year starts. The guide to getting the fiscal year from a date shows how to label the fiscal year too.

Other ways: helper column and pivot table#

A helper column is the easiest method to read. In C2, type =ROUNDUP(MONTH(A2)/3,0) and fill down. Then sum with =SUMIFS(B2:B9,C2:C9,2). Anyone who opens the file can see which quarter each row belongs to.

A pivot table is the fastest for a one-off report. Put Date in Rows and Amount in Values, right-click any date, choose Group, and select Quarters and Years. In newer versions, Excel may group dates automatically when you add them.

For other periods, the same patterns apply. See how to sum by month and how to sum by year.

Why is my quarterly total wrong?#

A wrong or zero total usually comes from one of these:

1. Dates stored as text. SUMIFS can't compare text with a date. If the dates sit on the left of the cell, convert them first.
2. A date typed inside the quotes. ">=1/4/2026" means 1 April in some regions and 4 January in others. Build dates with DATE.
3. Using "<=" with the last day when dates have times. 30 June at 3 pm is after 30 June at midnight, so "<="&DATE(2026,6,30) misses it. Use "<"&DATE(2026,7,1).
4. Forgetting the year. A quarter-number formula without a YEAR test mixes years together.
5. Ranges of different sizes. SUMIFS returns #VALUE! if B2:B9 and A2:A8 don't match.

Practise it#

Quarterly totals are a common task in finance and operations tests, and the hard part is writing date criteria quickly. Practise dates in criteria in a live spreadsheet, then practise SUMIFS with several conditions: you type each formula and ExcelReady checks it instantly. Start with the Q4 exercise on this page.