Explanation
In this example, the goal is to calculate total amounts in column C that occur in the last 30 days, based on a current date of December 30, 2022. There are three basic approaches to solving this problem: (1) a traditional approach based on the SUMIFS function , (2) a more flexible approach that uses Boolean logic and the SUMPRODUCT function , a modern approach based on the FILTER function . Each approach is explained below.
Excel date logic
To understand the logic of the formulas explained below, the first step is to understand Excel’s date system, where dates are serial numbers beginning on January 1, 1900. January 1, 1900 is 1, January 2, 1900 is 2, and so on. More recent dates are much larger numbers. For example, January 1, 1999 is 36161, January 1, 2010 is 40179, and January 1, 2020 is 43831. Because dates are just numbers, you can easily perform arithmetic on dates. For example, with January 1, 2020 in cell A1:
=A1+10 // Jan 11, 2020
=A1-10 // Dec 22, 2019
This also means we can use the TODAY function to return the current date, then subtract 30 days to get a date 30 days earlier. For example, as I write this, the current date is December 30, 2022 so:
=TODAY() // Dec 30, 2022
=TODAY()-30 // Nov 30, 2022
Therefore, to test if a date in cell A1 occurs within the last 30 days, we can use a formula like this:
=A1>=TODAY()-30
The above will return TRUE if the date is within the last 30 days of the current date and FALSE if not.
SUMIFS function
As mentioned above, the traditional way to solve a problem like this is to use the SUMIFS function. The SUMIFS function sums cells in a range that meet one or more conditions, referred to as criteria . To apply criteria, the SUMIFS function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. The generic syntax for SUMIFS looks like this:
=SUMIFS(sum_range,range1,criteria1) // 1 condition
where sum_range contains the values to sum, range is the cell range to test, and criteria1 is the criteria to apply to range . To solve this problem, we begin with the sum_range , which is C5:C16:
=SUMIFS(C5:C16,
Next, we add the range, which contains the dates in B5:B16:
=SUMIFS(C5:C16,B5:B16,
Finally, we add the criteria:
=SUMIFS(C5:C16,B5:B16,">="&TODAY()-30)
SUMPRODUCT function
Another way to solve this problem is with the SUMPRODUCT function and Boolean logic like this:
=SUMPRODUCT((B5:B16>=TODAY()-30)*C5:C16)
In this case, we are multiplying all values in C5:C16 by an expression designed to “zero out” values that should not be included in the final sum:
(B5:B16>=TODAY()-30)
The result from the expression above is an array of TRUE and FALSE values like this:
{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
The TRUE values in this array represent dates that occur in the last 30 days, based on a current date of December 30, 2022 returned by the TODAY function. These are the first five dates in the data as shown.
Next, the array above is multiplied by the values in C5:C16. In Excel, TRUE and FALSE values are automatically coerced to 1s and 0s by any math operation, so the multiplication step converts the arrays above to ones and zeros. We can visualize this operation inside of SUMPRODUCT like this:
=SUMPRODUCT({1;1;1;1;1;0;0;0;0;0;0;0}*C5:C16)
After multiplication, we have just a single array:
=SUMPRODUCT({5550;7450;1000;725;7150;0;0;0;0;0;0;0})
Notice that values associated with dates that are not within the past 30 days have been “zeroed out”. With just one array to process, SUMPRODUCT returns the sum of all items in the array: 21,875.
Note: Why would you use SUMPRODUCT instead of SUMIFS? The biggest reason is flexibility. Unlike SUMPRODUCT, SUMIFS requires a cell range ; you can’t use an array. This means SUMIFS won’t work in formulas that need to manipulate values before conditional logic is applied.
FILTER function
In the latest version of Excel, you can solve this problem with the FILTER function like this:
=SUM(FILTER(C5:C16,B5:B16>=TODAY()-30,0))
In this formula, we use the same logic explained above to target dates in the past 30 days:
B5:B16>=TODAY()-30 // last 30 days
The expression above returns an array of TRUE and FALSE values like this:
{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
And this array is used to filter the values in C5:C16. The result from FILTER is then returned directly to the SUM function:
=SUM({5550;7450;1000;725;7150})
SUM then returns a final result of 21,875.
Explanation
In this example, the goal is to sum the last n columns in a set of data, where n is a variable that can be changed at any time. In the latest version of Excel, the easiest way to solve this problem is with the TAKE function . In older versions of Excel you can use the OFFSET function , as explained below.
TAKE function
The TAKE function returns a subset of a given array or range . The size of the array returned is determined by separate rows and columns arguments . When positive numbers are provided for rows or columns, TAKE returns values from the start or top of the array. Negative numbers take values from the end or bottom of the array. For example:
=TAKE(array,3) // get first 3 rows
=TAKE(array,-3) // get last 3 rows
=TAKE(array,,3) // get first 3 columns
=TAKE(array,,-3) // get last 3 columns
In the worksheet shown, data is the named range C5:H16. We can retrieve the last 3 columns like this:
=TAKE(data,,-3) // last 3 columns
To make the number of columns variable, we need to swap in the reference to J5:
=TAKE(data,,-J5)
Finally, to sum the result from TAKE, we need to nest the TAKE function inside the SUM function :
=SUM(TAKE(data,,-J5))
With 3 in cell J5, the result from TAKE is the last 3 columns in data . This result is handed off to the SUM function, which returns a final result of 303, the sum of values in the range F5:H16.
Dynamic range option
One limitation of the formula above is that the named range data is static — it won’t expand as new columns are added. A simple way to make the range dynamic is to add the TRIMRANGE function to the formula like this:
=SUM(TAKE(TRIMRANGE(range,,-n)))
The TRIMRANGE function removes empty rows and columns from a range of data. The result is a “trimmed” range that only includes the used portion of the range. Because it is a formula, TRIMRANGE will update the range dynamically when data is added or removed from the original range. To make this work correctly, you will want to use a range that is largest enough to hold all possible data. For example, if the data begins in column A and might include up to 12 columns, you could use TRIMRANGE like this:
=SUM(TAKE(TRIMRANGE(A:L),,-n))
If needed, you could also remove the header row with the DROP function :
=SUM(TAKE(DROP(TRIMRANGE(A:L),1),,-3))
OFFSET function
In older versions of Excel, another way to solve this problem is to use the OFFSET function . The OFFSET function returns a reference to a range constructed with five inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a height in rows, (5) a width in columns. To sum the last 3 columns in the named range data , we can use the OFFSET function like this:
=SUM(OFFSET(data,0,COLUMNS(data)-J5,,J5))
Inside the OFFSET function, we provide data for reference and 0 for rows , since we don’t want any row offset. Next, we subtract the value for n in cell J5 from the total columns in data (6) to get a value for cols (3), the column offset. We leave height empty, because OFFSET will automatically inherit the height of reference , and we supply J5 for width , since we want a 3-column range in the end. In this configuration, OFFSET returns a 3-column range starting at cell F5, and this range contains 12 rows because the named range data contains 12 rows.
Note: the OFFSET function is a volatile function and can cause performance problems in larger or more complicated worksheets. If you run into this problem, see the INDEX solution below.
INDEX function
Yet another way to solve this problem is to use the versatile INDEX function in a formula like this:
=SUM(INDEX(data,0,COLUMNS(data)-(J5-1)):INDEX(data,0,COLUMNS(data)))
The key to understanding this formula is to realize that the INDEX function can return a reference to entire rows and entire columns . To generate a reference to the “last n columns” in a table, we build a reference in two parts: the left column and the right column, then use the range operator (:) to join the two parts together. To get a reference to the left column, we use:
INDEX(data,0,COLUMNS(data)-(J5-1))
Since data contains 6 columns, the COLUMNS function returns 6, and this simplifies to:
INDEX(data,0,4) // column 4
INDEX returns a reference to column 4. For the right column in the range, we use INDEX like this:
INDEX(data,0,COLUMNS(data))
Since COLUMNS returns 6, this simplifies to:
INDEX(data,0,6) // column 6
Together, the two INDEX functions return a reference to columns 4 through 6 in the data (i.e. F5:H16). The range operator (:) joins the two references together, and the SUM function returns a final result of 303.
