Explanation
In this example, the goal is to construct a formula that will subtotal the amounts in column D by client and month as seen in the range G5:I8. A big part of the problem is to set up the proper references so that the formula can be entered once, and copied throughout G5:I8. The solution explained below is based on the SUMIFS function.
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:
=SUMIFS(sum_range,range1,criteria1,range2,criteria2,...)
To sum amounts by client and month with SUMIFS, we will need to enter three criteria
- Client = client in column F
- Date >= first of month (from date in row 4)
- Date <= end of month (from date in row 4)
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 :
=SUMIFS(amount
Next, we need to enter the first range/criteria pair to target values in column B:
=SUMIFS(amount,client,$F5
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:
=SUMIFS(amount,client,$F5,date,">="&G$4
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:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
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 .
Explanation
In this example, the goal is to sum the amounts in column C by quarter in column G. Column D is a helper column , and the formula to calculate quarters from the dates in column B is explained below. All data is in an Excel Table named data in the range B5:E16. This problem can be solved with the SUMIFS function and the helper column, or without a helper column using the SUMPRODUCT function . Both approaches are explained below. Finally, you can also use an all-in-one dynamic array formula in the latest version of Excel.
Background study
- What is an Excel Table (3 min. video)
- Introduction to structured references (3 min. video)
- Excel Tables (overview)
Calculating quarters
The first step in this problem is to generate a quarter for each date in column B. In the table shown, column D is a helper column with quarter numbers calculated with a separate formula. The formula in D5, copied down, is:
=ROUNDUP(MONTH([@Date])/3,0) // get quarter
Note: because we are using an Excel Table to hold the data, we automatically get the structured reference seen above. The reference [@Date] means: current row in Date column. If you are new to structured references, see this short video: Introduction to structured references .
The MONTH function returns a month number between 1-12 for each date, which is divided by 3. The ROUNDUP function is then used to round the result to the nearest whole number. This formula is explained in more detail here .
SUMIFS solution
The next step in the problem is to add up the amounts in column C using the quarter numbers in column D. This can easily be done with the SUMIFS function . The SUMIFS function is designed to sum values in ranges conditionally based on multiple criteria. The signature of the SUMIFS function looks like this:
=SUMIFS(sum_range,range1,criteria1,range2,criteria2,...)
Notice the sum_range comes first, followed by range/criteria pairs. Each range/criteria pair of arguments represents another condition.
In this case, we need to configure SUMIFS to sum values by quarter using just one condition: we need to check the quarter in column D for a match in F5. We start off with the sum_range :
=SUMIFS(data[Amount]
Next, we add criteria as a range/criteria pair, where criteria_range1 is the Date column, and criteria1 is the quarter number in column F:
=SUMIFS(data[Amount],data[Qtr],F5)
As the formula is copied down, we get a total for each quarter in column F.
SUMPRODUCT without helper
To solve this problem without a helper column you can use a formula like this:
=SUMPRODUCT((ROUNDUP(MONTH(data[Date])/3,0)=F5)*data[Amount])
Working from the inside out, the first part of the expression inside the SUMPRODUCT function generates a quarter number for each date in the Date column like this:
ROUNDUP(MONTH(data[Date])/3,0)
This is basically the same formula used above, the difference is that we feed the MONTH function the entire data[Date] column instead of one cell. Because there are 12 dates in the column, we get back an array that contains 12 month numbers like this:
{1;2;3;4;5;6;7;8;9;10;11;12}
This array is delivered to the ROUNDUP function as the number argument:
ROUNDUP({1;2;3;4;5;6;7;8;9;10;11;12}/3,0)
And ROUNDUP returns an array of 12 quarter numbers:
{1;1;1;2;2;2;3;3;3;4;4;4}
Note: we are using a very small data set in this example for simplicity, but the same approach will work with hundreds or thousands of dates.
Next, the array from ROUNDUP is compared to F5 and the result is an array that contains 12 TRUE and FALSE values. When this array is multiplied by data[Amount] , the math operation changes the TRUE and FALSE values to 1s and 0s. At this point, we have:
=SUMPRODUCT({1;1;1;0;0;0;0;0;0;0;0;0}*data[Amount])
Multiplying the two arrays together results in a single array. In this array, only amounts associated with quarter 1 survive — amounts for other quarters are effectively “zeroed-out”:
=SUMPRODUCT({127;130;450;0;0;0;0;0;0;0;0;0})
With just one array to process, SUMPRODUCT sums the values in the array and returns a final result, 707. As the formula is copied down column G, it returns a total for each quarter, with no helper column needed.
Note: we use the SUMPRODUCT function in this formula for compatibility with older versions of Excel . In the current version of Excel, you can use the SUM function instead with the same result.
Dynamic array solution
In the latest version of Excel, which supports dynamic array formulas , it is possible to create a single all-in-one formula that builds the entire summary table, including headers, like this:
=LET(
dates,data[Date],
amounts,data[Amount],
quarters,ROUNDUP(MONTH(dates)/3,0),
uquarters,{1;2;3;4},
totals,BYROW(uquarters,LAMBDA(r,SUM((quarters=r)*amounts))),
VSTACK({"Quarter","Total"},HSTACK(uquarters,totals))
)
The LET function is used to assign values to five variables: dates , amounts, quarters, uquarters, and totals . First, we assign values to dates and amounts like this:
=LET(
dates,data[Date],
amounts,data[Amount]
Technically, we could just use the references data[date] and data[Amount] throughout the formula, but defining variables at the start keeps all worksheet references at the top of the code where they can be easily changed. You can easily adapt the formula to work with a different data set by editing just these two references.
Next, the value for quarters is created like this:
quarters,ROUNDUP(MONTH(dates)/3,0)
Here the MONTH function and the ROUNDUP function are used to calculate a quarter for each date in data[Date] . This formula is explained in more detail here . Because the table contains 12 rows of data, the result is an array with 12 quarter values like this:
{1;1;1;2;2;2;3;3;3;4;4;4}
In the next line we define uquarters (unique quarters) like this:
uquarters,{1;2;3;4}
Note we are just hardcoding the value as an array constant . Alternately, we could run quarters through the UNIQUE function . Either way, the result is a vertical array of four quarter numbers. At this point, we are ready to sum amounts by quarter. We do this with the BYROW function which calculates the sums and assigns the result to the variable counts for each year like this:
totals,BYROW(uquarters,LAMBDA(r,SUM((quarters=r)*amounts)))
BYROW runs through uquarters row by row. At each row, it applies this calculation:
LAMBDA(r,SUM((quarters=r)*amounts))
The value for r is the quarter number in the “current” row. Inside the SUM function, r is compared to quarters . Since quarters contains 12 values, the result is an array with 12 TRUE and FALSE results, which is then multiplied by amounts . The math operation automatically converts the TRUE and FALSE values to 1s and 0s, and the zeros effectively “zero out” amounts in other quarters. The SUM function then sums the resulting array and returns the result. When BYROW is finished, we have an array with six sums, which is assigned to totals :
{707;921;616;824} // totals
Finally the HSTACK and VSTACK functions are used to assemble a complete table:
VSTACK({"Quarter","Total"},HSTACK(uquarters,totals))
At the top of the table, the array constant {“Quarter”,“Total”} creates a header row. The HSTACK function combines uquarters and totals horizontally, and VSTACK combines the header row and the data to make the final table. The final result spills into multiple cells on the worksheet.
Pivot Table solution
A pivot table is another excellent solution when you need to summarize data by year, month, quarter, and so on, because it can do this kind of grouping for you without any formulas at all. For a side-by-side comparison of formulas vs. pivot tables, see this video: Why pivot tables .
