All data is in the range B5:J16 andnis entered into cell K2 as 3.
The value forncan be changed at any time.
InLegacy Excel,you could use approach #2 and a formula based on the SUMPRODUCT function.
Both approaches are explained below.
Note: The approaches below all depend on theMOD functionto work out which values to sum.
These are the values we want to sum.
The array above is returned to FILTER as theincludeargument.
FILTER uses this array to “filter” values in the range B5:J16by column.
Only values associated with TRUE make it through the filter operation.
The result is an array that contains every 3rd value in the data.
This formula is dynamic.
Rather than extracting values of interest from the data, this formula “zeros out” valuesnot of interest.
Adouble negative(–) is used to convert the TRUE and FALSE values to 1s and 0s.
This formula is also dynamic.
This formula will only work in the current version of Excel.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
…
SUMPRODUCT Function
The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.
For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet.
When no reference is provided, COLUMN returns the column number of the cell which contains the formula.
MOD Function
The Excel MOD function returns the remainder of two numbers after division.
For example, MOD(10,3) = 1.
The result of MOD carries the same sign as the divisor.
