where amount (D5:D16), client (B5:B16), and date (C5:C16) are named ranges.
Summary table setup
The first step in solving this problem is creating the summary table seen in the range F4:I8. The values in F4:F9 are text values. However, the month names in G4:I5 are actually valid Excel dates, formatted with the custom number format “mmm”:
SUMIFS solution
The SUMIFS function can sum values in ranges based on multiple criteria. Multiple criteria are entered in range/criteria pairs like this: To sum amounts by client and month with SUMIFS, we will need to enter three criteria The first step in configuring SUMIFS is to enter the sum_range, which contains the values to sum in column D. For sum_range, we use the named range amount: Next, we need to enter the first range/criteria pair to target values in column B: Notice F5 is a mixed reference, with the column locked and the row relative. This allows the row to change as the formula is copied down the table, but the client name in column F does not change as the formula is copied to the right. Next, we need to enter the second range/criteria pair, which is used to target dates that are greater than or equal to the month in G4. For the range, we use the named range date. For criteria, we use the greater than or equal to (>=) operator concatenated to the value in G4: Notice G$4 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 as the formula is copied down. The concatenation with an ampersand (&) is necessary when building criteria that use a logical operator and a value from another cell, because the SUMIFS function is in a group of eight functions that split criteria into two parts. Finally, we need to enter the third range/criteria pair to check dates against the last day of the month. For the range, we again use the named range date. For criteria, we use the less than or equal to (<=) operator concatenated to the EOMONTH function: We use the EOMONTH function to get the last day of the month of the date in G4. As before, we need to concatenate the result from EOMONTH to the logical operator. Again, the reference to G$4 is mixed to keep the row from changing. As the formula is copied into the range G5:I8, the SUMIFS function returns a sum for each client and month.
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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
