Excel Practice
Lessons Lesson 84

Grouping by Month: EOMONTH and SUMIFS

About this lesson

A monthly total in Excel is SUMIFS with two conditions on the date column. On or after the month's first day, and on or before the day EOMONTH returns. So one formula fills down a column of months.

By the end you can

  • total a month with SUMIFS and EOMONTH.
  • label months with TEXT and extend a monthly report with EOMONTH.

Practises SUMIFS COUNTIFS EOMONTH TEXT

The idea

There is no month function to group by, and none is needed. A month is a range of dates: from its first day to the day EOMONTH(start, 0) gives back. Two conditions on the date column, and SUMIFS, COUNTIFS or AVERAGEIFS does the rest. Write the operator in quotation marks and join it to the cell with &. ">=E2" with the cell inside the quotes compares against the letters E and 2.

EOMONTH is the quiet hero. It knows February, leap years and thirty-day months. EOMONTH(start, 0) + 1 is the first of next month, which is how the report extends itself.

The mistake to watch for

Putting the cell inside the quotation marks. A condition written as ">=A2" compares against the letters A and 2. It matches nothing, and the month total is 0 with no error. The operator goes in quotes and is joined to the cell with &. And the month end comes from EOMONTH, never from adding 30 days.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In B2, show the month in A2 by name, with TEXT and the format mmmm.

To begin, type it exactly:

=TEXT(A2,"mmmm")

B2
Row A B C D E F G
1 Month start Month Total Items Date Amount Shown
2 45292 45295 120 4 Jan
3 45323 45301 80 10 Jan
4 45352 45318 45 27 Jan
5 45330 210 8 Feb
6 45341 60 19 Feb
7 45355 95 4 Mar
8 45362 130 11 Mar
9 45372 70 21 Mar

Every step

  1. 1

    In B2, show the month in A2 by name, with TEXT and the format mmmm. To begin, type it exactly: =TEXT(A2,"mmmm").

    Hint. Format "mmmm".

  2. 2

    EOMONTH gives the last day of a month. Read =EOMONTH(A3,0) and say what serial it returns for February 2024.

    Hint. February 2024 has 29 days.

  3. 3

    In C2, total January's amounts with SUMIFS. Dates on or after the month start in A2, and on or before the end of that month from EOMONTH. Lock the data ranges so the formula fills down.

    Hint. ">="&A2 and "<="&EOMONTH(A2,0).

  4. 4

    In D2, count January's items with COUNTIFS and the same two conditions.

    Hint. Same two conditions.

  5. 5

    In A5, write a formula using EOMONTH that gives the first day of the month after March. Then the report is ready to extend.

    Hint. End of month, plus one.