Tab of the Month: last month's total for each location in one formula

The tab

Month end, first week of the new month. The payments log has a few hundred rows, one per payment, with a date, a location and an amount, and someone wants last month's total for each location by lunch. The usual way is a filter, a selection, a glance at the status bar and a number typed into another tab, once per location, every month.

Last month, by location, from one formulaDenver$2,608.33OctoberPhoenix$1,600.00OctoberPortland$850.00OctoberTotal$5,058.33OctoberSample figures · =SUMIFS with EOMONTH(TODAY(),-1)

One formula does it, and it never needs its dates changed.

The sample log

Eight payments, with figures made up for the example. Dates are in column A, the location in column B and the amount in column C, with headers in row 1.

It's early November, and we want October. The two Denver payments at the top and bottom of the log are the traps: one belongs to September, the other to November.

Which formula totals a location's month?

Put the location names in column E (Denver in E2, Phoenix in E3, Portland in E4) and this in F2:

=SUMIFS($C$2:$C$9, $B$2:$B$9, E2, $A$2:$A$9, ">"&EOMONTH(TODAY(),-2), $A$2:$A$9, "<="&EOMONTH(TODAY(),-1))

Then copy it down to F4. Read it in pieces:

• $C$2:$C$9 is what gets added up. In SUMIFS the range to sum comes first (in the older SUMIF it comes third, which catches everyone once).
• $B$2:$B$9, E2 keeps only the rows whose location matches the name in E2.
• ">"&EOMONTH(TODAY(),-2) keeps rows dated after the last day of the month before last. In early November that's September 30.
• "<="&EOMONTH(TODAY(),-1) keeps rows dated on or before the last day of last month: October 31.

A row is counted only when it passes every test. The dollar signs make the ranges absolute, so they stay put when the formula is copied down, while E2 becomes E3 and E4.

What it gives

Denver is $1,108.33 plus $1,500.00. The September 30 and November 1 payments stay out, and Portland's October 31 payment stays in.

Why EOMONTH and not typed dates?

EOMONTH(start_date, months) returns the last day of the month a given number of months before or after the start date: a negative number goes back, a positive one goes forward. TODAY() returns the current date and updates whenever the sheet recalculates. Together they always point at last month, so the same tab works in December for November, and in January for December, with nothing retyped.

The ">"& part glues a comparison onto a value, which is how a criterion like "after this date" is written when the date comes from a cell or a formula rather than being typed in quotes.

What if you want a month that isn't last month?

Because TODAY() moves, a tab opened in March will quietly show February. If the tab is a record of a month you've closed, pin the month instead. Type the month's last day in H1 (10/31/2026) and change the two date tests to:

">"&EOMONTH($H$1,-1) and "<="&$H$1

Now the tab says October until someone changes H1.

What if your dates carry times?

Some logs stamp each payment with a time as well as a date. In Excel a time is a fraction of a day (noon is 0.5), so a payment at 3pm on October 31 is a little more than October 31 and fails the "<=" test. The same thing happens at the other end: a payment at 3pm on September 30 is a little more than September 30, so it passes the ">" test and lands in October. The fix is to change both tests, so the month runs from the first moment of its first day up to, but not including, the first moment of the next month:

">="&EOMONTH(TODAY(),-2)+1 and "<"&EOMONTH(TODAY(),-1)+1

Adding 1 to a date in Excel adds one day, since dates are stored as serial numbers. For a pinned month in H1, the same pair is ">="&EOMONTH($H$1,-1)+1 and "<"&$H$1+1.

Does it work in Google Sheets?

Yes. SUMIFS, EOMONTH and TODAY take the same arguments in the same order in Google Sheets, Sheets counts dates as days just as Excel does, and its help pages build criteria the same way, with a comparison glued to a date using &.

Sources

• Microsoft Support, SUMIFS function, SUMIF function, EOMONTH function, TODAY function, TIME function and Switch between relative, absolute, and mixed references
• Google Docs Editors Help, SUMIFS, EOMONTH, TODAY, DATE and COUNTIFS