How to Multiply Non-Adjacent Cells in Excel Based on Dynamic Criteria

Manually multiplying disjointed data points across dynamic quarterly budgets is a common, error-prone headache for financial analysts. Typically, organizations rely on standard funding sources like operational budgets, which use static formulas. However, federal grants offer highly flexible, performance-linked funding that requires advanced, dynamic Excel modeling to track. Under strict reporting stipulations, these calculations must align perfectly with fluctuating compliance criteria. For instance, tracking Title IV or NSF grant distributions requires precise non-adjacent cell multiplication based on shifting department codes. Below, we will explore the exact Excel formula framework using SUMPRODUCT and FILTER to seamlessly automate this dynamic calculation.

Excel is an incredibly powerful tool for data analysis, but it often throws complex challenges our way. One such challenge is performing calculations across non-adjacent (non-contiguous) cells. While adding non-adjacent cells is relatively straightforward using the SUM function, multiplying them based on dynamic, changing criteria can quickly become a headache.

If you have ever tried to write a formula like =A2 * D2 * G2, you already know how fragile this approach is. If a user inserts a new column, if your criteria change, or if you need to scale the formula across thousands of rows, manual multiplication falls apart. To build a robust, future-proof spreadsheet, you need a dynamic solution. This guide will walk you through the best Excel formulas to multiply non-adjacent cells based on dynamic criteria, covering both modern Excel (Microsoft 365 and Excel 2021) and legacy versions.

The Core Challenge: Why Standard Formulas Fail

To understand the solution, we must first understand the limitation. Most of Excel's array-processing functions, such as SUMPRODUCT or FILTER, expect contiguous ranges (e.g., A2:E2). When you try to pass non-adjacent ranges like (A2, C2, E2) into these functions, Excel often returns a #VALUE! or #REF! error because it cannot process disjointed referenced areas as a single cohesive array.

To overcome this, we must use formulas that can either:

• Dynamically reconstruct a contiguous array from non-adjacent columns using modern array manipulation.
• Identify target columns programmatically using headers or positions (such as "every 3rd column").
• Apply logical criteria to filter out the values we do not want to multiply.

Method 1: Using CHOOSECOLS and FILTER (Best for Microsoft 365)

If you are using Microsoft 365 or Excel 2021, the dynamic array engine makes this task significantly easier. By combining CHOOSECOLS, FILTER, and BYROW (or PRODUCT), you can dynamically target specific non-adjacent columns and multiply them row-by-row based on a criteria column.

The Scenario

Imagine you have a dataset containing sales projections. Your columns are structured as follows:

• Column A: Region (Criteria: e.g., "North")
• Column B: Base Units (To Multiply)
• Column C: Warehouse Location (Ignore)
• Column D: Markup Factor (To Multiply)
• Column E: Shipping Tier (Ignore)
• Column F: Tax Multiplier (To Multiply)

We want to multiply Base Units (Col B) * Markup Factor (Col D) * Tax Multiplier (Col F), but only for the rows where the Region (Col A) is "North".

The Formula

=LET(
    data, A2:F10,
    criteria, INDEX(data, 0, 1),
    filtered_rows, FILTER(data, criteria = "North"),
    target_cols, CHOOSECOLS(filtered_rows, 2, 4, 6),
    BYROW(target_cols, LAMBDA(row, PRODUCT(row)))
)

How It Works Step-by-Step:

1. LET Function: We use LET to define variables, making the formula easier to read and faster to calculate.
2. criteria: Points to the first column (Region) within our data range.
3. filtered_rows: The FILTER function extracts only the rows from our dataset where the Region is "North".
4. target_cols: CHOOSECOLS extracts only our non-adjacent columns (2nd, 4th, and 6th columns, which represent columns B, D, and F) from the filtered dataset.
5. BYROW and LAMBDA: Since PRODUCT normally multiplies an entire array into a single number, we use BYROW to force Excel to apply the PRODUCT function row-by-row, returning a dynamic spill range of the final multiplied values.

Method 2: Dynamically Selecting Columns by Header Name

Hardcoding column index numbers (like 2, 4, 6) can still cause issues if someone rearranges the columns. A more dynamic approach is to search for specific column headers (e.g., "Units", "Markup", "Tax") and multiply those columns matching our criteria row-by-row.

The Formula

=LET(
    headers, A1:F1,
    data, A2:F10,
    regions, A2:A10,
    col_B, INDEX(data, 0, MATCH("Base Units", headers, 0)),
    col_D, INDEX(data, 0, MATCH("Markup Factor", headers, 0)),
    col_F, INDEX(data, 0, MATCH("Tax Multiplier", headers, 0)),
    IF(regions = "North", col_B * col_D * col_F, "")
)

Why This Method is Exceptionally Robust:

• Position Independent: You can move the "Markup Factor" column from Column D to Column E, and the formula will not break. The MATCH function dynamically finds the new position.
• Simple Multiplication: By using the asterisk operator (*) to multiply the retrieved columns, Excel performs element-by-element array multiplication across the specified rows.
• Conditional Logic: The IF statement ensures that multiplication only occurs for rows matching "North". Unmatched rows will return an empty string ("").

Method 3: Multiplying Every Nth Column Based on Periodic Criteria

Sometimes, your non-adjacent cells follow a strict mathematical pattern rather than text-based headers. For example, you might have a sheet tracking monthly performance where every 3rd column contains a "Growth Rate" that you want to multiply together to get a cumulative rate, filtered by a specific department.

The Formula (Using MOD and COLUMN)

To multiply every 3rd column in a range (e.g., columns C, F, I) for rows matching a department name in Column A:

=SUMPRODUCT(
    (A2:A10 = "Sales") * 
    PRODUCT(IF((MOD(COLUMN(C2:I10) - COLUMN(C2), 3) = 0), C2:I10, 1))
)

Note: Depending on your Excel version, this may require entering with Ctrl + Shift + Enter (CSE) if you are not on Microsoft 365.

How It Works:

• COLUMN(C2:I10) - COLUMN(C2): This normalizes the column numbers so that the first column in your calculation range starts at 0.
• MOD(..., 3) = 0: The MOD function returns the remainder of a division. Checking if it equals 0 isolates every 3rd column (0, 3, 6, etc.).
• IF(..., C2:I10, 1): If the column matches our modulo criteria, we keep the cell value. If it doesn't, we return 1. This is a crucial mathematical trick: multiplying a number by 1 does not change its value, allowing us to safely ignore the intermediate non-adjacent columns.

Method 4: The Legacy Solution (Excel 2019 and Older)

If you are working on an older version of Excel that lacks dynamic arrays (no FILTER, LET, or CHOOSECOLS), you can still solve this using a combination of SUMPRODUCT and boolean algebraic multiplication.

=SUMPRODUCT((A2:A10="North") * B2:B10 * D2:D10 * F2:F10)

Why This Works:

In Excel, boolean TRUE/FALSE values resolve to 1 and 0 when subjected to mathematical operations. By multiplying our criteria array (A2:A10="North") against our target columns:

• If a row matches "North", the calculation becomes: 1 * B2 * D2 * F2 (yielding the correct product).
• If a row does not match "North", the calculation becomes: 0 * B2 * D2 * F2 (yielding 0).

While this method lacks the elegant column-finding automation of modern Excel, it is incredibly fast, backward-compatible, and requires no special array activation in most scenarios.

Best Practices for Working with Non-Adjacent Calculations

To keep your dynamic workbooks clean and performant, keep these best practices in mind:

1. Use Named Ranges: Instead of raw cell references like B2:B10, define a Named Range (e.g., Base_Units). This makes your dynamic multiplication formulas significantly easier to read and maintain.
2. Handle Empty Cells Safely: Blank cells in a multiplication range can resolve to 0, completely wiping out your product calculation. Wrap your targeted ranges in an IF statement to substitute blanks with 1 if necessary (e.g., IF(ISBLANK(range), 1, range)).
3. Avoid Volatile Functions: Avoid using functions like OFFSET to target non-adjacent columns. OFFSET forces Excel to recalculate the entire formula every time any cell in the sheet changes, which can severely slow down large workbooks. Stick to INDEX and CHOOSECOLS instead.

Summary

Multiplying non-adjacent cells based on dynamic criteria doesn't have to result in messy, manual formulas. By leveraging modern array tools like CHOOSECOLS and FILTER, or utilizing classic lookup methods like SUMPRODUCT and INDEX/MATCH, you can create dynamic models that adapt to shifting data configurations without breaking. Choose the method that best aligns with your Excel version and start building more resilient spreadsheets today.