Formula example

SUMIFS by Month Formula Example

Q: Does this work in Google Sheets?

Yes. SUMIFS, DATE, YEAR, MONTH, and EOMONTH are available in Google Sheets.

Q: Can I use a typed month name?

A real date cell is more reliable than a typed month name.

Q: Why use less than next month instead of less than or equal to month end?

The less-than-next-month pattern includes all rows on the last visible day even when source dates contain time values.

Q: Can this ignore the year and total every January?

Not with this SUMIFS boundary pattern. It totals one selected month in one selected year. Use a month-number pattern when the year should be ignored.

You have daily sales rows and want to total the Amount column for all rows that fall inside the month stored in F1. The safest pattern uses SUMIFS with a start-of-month boundary and a before-next-month boundary, so it works in both Excel and Google Sheets.

Copyable formula

Excel and Google Sheets formula

=SUMIFS(D2:D100, A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1)

What it returns

If F1 is 2026-01-01 and the sample data is used, the formula returns 730 because it adds the January rows: 420 + 310.

Useful variations

Hardcoded January 2026 version

=SUMIFS(D2:D100, A2:A100, ">="&DATE(2026,1,1), A2:A100, "<"&DATE(2026,2,1))

Use this only when the month will not change. A month cell is easier to reuse.

SUMIFS by month and region

=SUMIFS(D2:D100, A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1, B2:B100, "East")

Add another criteria pair when you need a month total for one region, product, or status.

Current month version

=SUMIFS(D2:D100, A2:A100, ">="&EOMONTH(TODAY(),-1)+1, A2:A100, "<"&EOMONTH(TODAY(),0)+1)

Use this when the report should always total the current calendar month.

Sample data

Date	Region	Product	Amount
2026-01-04	East	Widget	420
2026-01-12	West	Widget	310
2026-02-03	East	Gadget	275
2026-02-15	East	Widget	640

When to use this formula

Use this when source rows contain daily dates and you want one monthly total.
Use a real date cell such as 2026-01-01 in F1, even if the cell is formatted to show only January.
Use the SUMIFS builder when you need to add product, region, owner, or status criteria.
Use the formula reference page when you need reusable month-cell, current-month, and extra-criteria variations together.

Date boundary pattern

The lower boundary is the first day of the selected month. The upper boundary is the first day of the next month, and the formula uses the less-than operator so rows with time values still stay inside the correct month.

Reusable month cell pattern

=SUMIFS(D2:D100, A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1)

Put any real date from the target month in F1.

Google Sheets support

Google Sheets supports the same SUMIFS, DATE, YEAR, MONTH, and EOMONTH functions used in this Excel example. The important part is to keep F1 as a real date value, not typed month text.

Google Sheets month total

=SUMIFS(D2:D100, A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1)

The syntax is the same in Google Sheets.

Troubleshoot monthly totals

If the result is zero, first test whether the date cells are real dates. Then confirm the date range and amount range cover the same rows, and that F1 is not stored as text.

If the end-of-month row is missing, avoid a <= last-day test and use < first-day-of-next-month instead.

Returned totals from the sample data

Formula setup	Returned result	Rows included
F1 contains any January 2026 date	730	2026-01-04 East Widget 420 plus 2026-01-12 West Widget 310.
F1 contains any February 2026 date	915	2026-02-03 East Gadget 275 plus 2026-02-15 East Widget 640.
January 2026 plus Region = East	420	Only the 2026-01-04 East Widget row.

Which monthly total pattern to use

Need	Use this pattern	Why
A reusable selected month	SUMIFS with DATE and EOMONTH based on F1	The report owner can change F1 without editing the formula.
The current calendar month	SUMIFS with EOMONTH(TODAY(), -1)+1 and EOMONTH(TODAY(), 0)+1	The report updates automatically as the month changes.
One month plus another condition	SUMIFS month boundaries plus another criteria pair	Region, product, owner, or status can be added without changing the date logic.
Month name regardless of year	SUMPRODUCT with MONTH comparison	SUMIFS month boundaries intentionally keep the year in the result.

Formula explanation

The first date criterion starts at the first day of the selected month.
The second date criterion stops before the first day after that month.
Using date boundaries is safer than comparing formatted month text.

Common errors

F1 must contain a real date, not only the text January.
The date range and sum range must cover the same rows.
Use < next month instead of <= end of month when source values may contain times.
Do not use this pattern if the report should combine January from every year; that needs a different month-number formula.

Build your own version

Use the formula builder for this pattern: SUMIFS Formula Builder.

Related formulas

FAQ

Does this work in Google Sheets?

Yes. SUMIFS, DATE, YEAR, MONTH, and EOMONTH are available in Google Sheets.

Can I use a typed month name?

A real date cell is more reliable than a typed month name.

Why use less than next month instead of less than or equal to month end?

The less-than-next-month pattern includes all rows on the last visible day even when source dates contain time values.

Can this ignore the year and total every January?

Not with this SUMIFS boundary pattern. It totals one selected month in one selected year. Use a month-number pattern when the year should be ignored.