Explanation
In this example, the goal is to sum every nth value in a range of data, as seen in the worksheet above. For example, if n =2, we want to sum every second value (every other value), if n =3, we want to sum every third value, and so on. All data is in the range B5:B16 and n is entered into cell F5 as 3. This value can be changed at any time. In the latest version of Excel , the easiest way to do this is to use the FILTER function. In Legacy Excel, you can use an alternative formula based on the SUMPRODUCT function as explained below.
Example formula
In the example shown, the formula in cell F6 is:
=SUM(FILTER(B5:B16,MOD(SEQUENCE(ROWS(B5:B16)),F5)=0))
At a high level, this formula uses the FILTER function to extract values associated with every nth row of the data, and the SUM function to sum the values extracted.
Extracting data
Working from the inside out, the first step in this problem is to collect the data that should be summed. This is done with the FILTER function like this:
FILTER(B5:B16,include)
where include represents the formula logic needed to target every nth value (every 3rd value in the example). To construct the logic we need, we use a combination of the MOD function, the SEQUENCE function, and the ROWS function:
MOD(SEQUENCE(ROWS(B5:B16)),F5)=0)
The ROWS function returns the count of rows in the range B5:B16, which is 12:
MOD(SEQUENCE(12),F5)=0)
With 12 as the rows argument, the SEQUENCE function returns a numeric array of 12 numbers like this:
{1;2;3;4;5;6;7;8;9;10;11;12}
Substituting the array above and the value for n (3) into the formula we have:
MOD({1;2;3;4;5;6;7;8;9;10;11;12},3)=0)
The MOD function returns the remainder of each number in the array divided by 3:
{1;2;0;1;2;0;1;2;0;1;2;0}=0
The result from MOD is compared to zero, which creates an array of TRUE and FALSE values:
{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}
Note that every third value is TRUE. This is the value returned to FILTER as the include argument. FILTER uses this array to “filter” values in the range B5:B16. Only values associated with TRUE make it through the filter operation. The result is an array that contains every 3rd value in the data. Since there are 12 values total, FILTER returns 4 values:
{20;15;10;25}
FILTER delivers this array of values directly to the SUM function, which returns the sum (70) as a final result:
SUM ({20;15;10;25}) // returns 70
This formula is dynamic. For example, if the value for n in cell F5 is changed to 2 (every 2nd value) the new result is 120.
Legacy Excel formula
In older versions of Excel that do not include the FILTER or SEQUENCE functions, you can use a different formula based on the SUMPRODUCT function:
=SUMPRODUCT(--(MOD(ROW(B5:B16)-ROW(B5)+1,F5)=0),B5:B16)
The concept is similar to the formula explained above but the approach is different. Rather than extract values of interest from the data, this formula “zeros out” the other values not of interest. First, the formula uses the ROW function to construct a relative set of row numbers :
ROW(B5:B16)-ROW(B5)+1
The result is a numeric array like this:
{1;2;3;4;5;6;7;8;9;10;11;12}
Inside the SUMPRODUCT function, we again use the MOD function to construct a filter:
=MOD(ROW(B5:B16)-ROW(B5)+1,F5)=0
=MOD({1;2;3;4;5;6;7;8;9;10;11;12},F5)=0
MOD returns an array of TRUE FALSE values like this:
{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}
Again, note that every 3rd value is TRUE. A double negative (–) is used to convert the TRUE and FALSE values to 1s and 0s. Back in the SUMPRODUCT, we now have:
=SUMPRODUCT({0;1;0;1;0;1;0;1;0;1;0;1},B5:B16)
The SUMPRODUCT then multiplies the two arrays together and returns the sum of products. Only the values in B5:B6 that are associated with 1s survive this operation, the other values are “zeroed out”:
=SUMPRODUCT({0;0;20;0;0;15;0;0;10;0;0;25}) // returns 70
The final result is 70. This formula is also dynamic. If the value for n in cell F5 is changed to 2 (every 2nd value) the new result is 120.
Explanation
In this example, the goal is to sum the first n matching values in a set of data. Specifically, we want to sum the first 3 values for both Red and Blue, based on the order they appear in the table. There are 12 values total; 6 entries each for Red and Blue. All data is in Excel Table named data in the range B5:C16.
Example formula
In the example shown, the formula in cell G5, copied down, is:
=SUM(TAKE(FILTER(data[Qty],data[Color]=F5),3))
Notice the value for n is hardcoded as 3. This formula is a good example of nesting one function inside another.
Extracting matching data
Working from the inside out, the first task is to extract a list of quantities by color. This is done with the FILTER function like this:
FILTER(data[Qty],data[Color]=F5)
With “Red” in cell F5, the result is an array that contains quantities associated with “Red”:
{6;5;6;9;6;8}
Notice there are 6 numbers in this array, one for each entry where the color is Red.
Extract first 3 values
The next task is to extract just the first 3 values from the array returned by FILTER. This is done with the TAKE function . FILTER returns the array directly to the TAKE function as the array argument, with the rows argument hardcoded as 3:
TAKE({6;5;6;9;6;8},3) // returns {6;5;6}
The TAKE function then returns the first 3 values in the array:
{6;5;6}
Sum results
The last step in the problem is to sum the results from FILTER and TAKE. This is done with the SUM function . TAKE returns the first 3 values to SUM:
=SUM({6;5;6}) // returns 17
And SUM returns 17 as a final result. This is the sum of the first 3 quantities for “Red”. When the formula is copied down to cell G6, we get a sum of the first 3 “Blue” quantities.
Sum last n matching values
To sum the last n matching values, simply change n to a negative number like this:
=SUM(TAKE(FILTER(data[Qty],data[Color]=F5),-3))
The TAKE function is explained in more detail here .
