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

By Ali, Founder, Excel Lessons

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.

Read more about this

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, type: =TEXT(A2,"mmmm"). mmmm shows the month's full name.

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, type: =TEXT(A2,"mmmm"). mmmm shows the month's full name.

    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

    The month runs from its start to the day EOMONTH returns. In C2, total January's amounts with SUMIFS. Lock the 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, find the first day of the month after March with EOMONTH. Shape: =EOMONTH(cell,0)+n. Then the report is ready to extend.

    Hint. End of month, plus one.