Exceljet

Quick, clean, and to the point

Sum by month in columns

Excel formula: Sum by month in columns
Explanation 

To sum by month in columns you can use the SUMIFS function together with the EOMONTH function. In the example shown, the formula in G5 is:

=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))

This formula uses three named ranges: "amount" (D5:D15), "client" (B5:B15), and "date" (C5:C15).

How this formula works

The SUMIFS function is designed to sum values in in a range based on one or more criteria. In this case, we need three criteria:

  1. Client name = client in column F
  2. Date >= first of month (from date in row 4)
  3. Date <= end of month (from date in row 4)

Key point: the month names in G4:I4 are actually valid dates, formatted with the custom number format "mmm". This allows us to use the date values in G4:I4 directly for criteria #2 and #3 above. 

Back in SUMIFS, the first range/criteria pair is used to check the client name:

client,$F5

Notice F5 is a mixed reference, with the column locked. This allows the row to change as the formula is copied through the table, but the client name always comes from column F.

The second range/criteria pair is used to check dates against the first of the month:

date,">="&G$4

As mentioned above, values in G4:I4 are actually dates: Jan 1, 2019, Feb 1, 2019, and Mar 1, 2019. So, this criteria simply checks for dates greater than the first of month dates in row 4. Notice that this is another mixed reference, this time with the row locked. This allows the column to change as the formula is copied across the table, but keeps the row number fixed. The concatenation with an ampersand (&) is necessary when building criteria that use a logical operator and a value from another cell.

The third range/criteria pair is used to check dates against the last day the month:

date,"<="&EOMONTH(G$4,0)

To get the last day of each month, we use the EOMONTH function on the date from the column header in row 4. With zero provided for the months argument, the EOMONTH returns the last day of same month. Again, the reference to G4 is mixed to keep the row from changing.

Pivot Table solution

A pivot table would be an excellent solution for this problem, because it can automatically group by month without any formulas at all. For a side-by-side comparison of formulas vs. pivot tables, see this video: Why pivot tables.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.