Sum by month in columns
This formula uses three named ranges: "amount" (D5:D15), "client" (B5:B15), and "date" (C5:C15).
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:
- Client name = client in column F
- Date >= first of month (from date in row 4)
- 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:
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:
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:
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.