Total amounts by month with SUMIFS

Sample data. Dates in A, amounts in B, the first day of each month in E (shown as Jan 2026), the formula in F2 is copied down.

The formula

In column E, type the first day of each month (1 Jan 2026 in E2, 1 Feb 2026 in E3, and so on). Click F2 and type:

=SUMIFS(B:B,A:A,">="&E2,A:A,"<="&EOMONTH(E2,0))

F2 shows 200.00: 120 on 5 Jan plus 80 on 18 Jan. Copy it down and February shows 210.00 and March 245.00.

• B:B is the column to add up.
• A:A,">="&E2 keeps dates on or after the first of the month.
• A:A,"<="&EOMONTH(E2,0) keeps dates on or before the last day of that month. EOMONTH(E2,0) is 31 Jan 2026.

The & joins the sign to the date, so only the sign goes in quotes. Your dates must be real dates, not text (see the last section).

Show the month name

E2 holds a full date, 1 Jan 2026. To show it as Jan 2026, give column E a custom date format. In Excel, select the cells, press Ctrl+1, choose Custom on the Number tab and type mmm yyyy in the Type box. In Google Sheets, select the cells, then Format > Number > Custom date and time. The formula still sees the date underneath. If any cell shows a number like 46053 instead of a date, it only needs a date format.

One category in one month

Add one more range and condition pair at the end:

=SUMIFS(B:B,A:A,">="&E2,A:A,"<="&EOMONTH(E2,0),C:C,"Fuel")

Fuel in January is 120. Change E2 to E4 and Fuel in March is 45: the 31 March entry counts, because the last day of the month is included.

Count and average by month

The same date conditions work in COUNTIFS and AVERAGEIFS. How many entries in January?

=COUNTIFS(A:A,">="&E2,A:A,"<="&EOMONTH(E2,0))

That gives 2. The average amount in January:

=AVERAGEIFS(B:B,A:A,">="&E2,A:A,"<="&EOMONTH(E2,0))

That gives 100, and with E4 March gives 122.5.

This month so far

EOMONTH(TODAY(),-1) is the last day of last month, so adding 1 gives the first day of this month. Total everything from then up to today:

=SUMIFS(B:B,A:A,">="&EOMONTH(TODAY(),-1)+1,A:A,"<="&TODAY())

It updates by itself every day and starts again at 0 on the first of each month.

Dates with a time attached

Exports and form responses often store a time with the date. An entry at 2:30 PM on 31 Jan is later than 31 Jan itself, so the "<=" end of month misses it. Ask for dates before the first day of the next month instead:

=SUMIFS(B:B,A:A,">="&E2,A:A,"<"&EDATE(E2,1))

EDATE(E2,1) is 1 Feb 2026. With plain dates it gives the same 200; with a 50.00 entry at 2:30 PM on 31 Jan, the first formula leaves it out and this one counts it.

The total shows 0

The usual cause: the dates are text that only looks like dates, often after a paste or an import. Text dates tend to sit on the left of the cell. Check one:

=ISNUMBER(A2)

TRUE means a real date. FALSE means text, and SUMIFS skips it. DATEVALUE turns text like 2026-01-05 into a real date:

=DATEVALUE(A2)

Put it in a spare column, copy it down, give it a date format, then paste the results back as values. SUMIFS, COUNTIFS, AVERAGEIFS, EOMONTH and EDATE work in every current version of Excel and in Google Sheets.

Templates with the math already done

Rather skip the formulas? These templates come with every formula built and checked, plus a filled-in sample.