Formula Errors

How to Sum Every Fourth Column Before a Specific Date using SUMPRODUCT in Excel

Algirdas Jasaitis Oct 15, 2026 871 views

Question details

The user needs an Excel formula to sum values from specific columns (spaced out every fourth column) only if the corresponding date in another set of columns is earlier than or equal to a target date in cell A1.

How to Use Excel SUMPRODUCT to Sum Every Fourth Column Before a Date

Product: Excel
Device & OS: not provided
Scenario: Calculating conditional totals across staggered or periodic columns based on a specific date threshold.
Observed behavior: The goal is to correctly evaluate the date criteria in a staggered arrangement and sum the corresponding values dynamically.

Before you start

Ensure your date values are formatted as actual Excel dates (not text strings) and note the exact cell reference of your threshold date (e.g., cell A1) to lock it as an absolute reference.

Solution 1Recommended

Use the SUMPRODUCT Function with Offset Ranges

Utilize the SUMPRODUCT function to evaluate date criteria in one set of staggered columns and sum the corresponding values in another staggered range.

The SUMPRODUCT function can evaluate arrays seamlessly without requiring complex helper columns. By offsetting the range for the sum compared to the range for the date criteria, you can target specific periodic columns (like every fourth column).

Select the target cell

Click on the cell where you want the calculated total to appear, such as cell Z2.

Enter the SUMPRODUCT formula

Type the formula =SUMPRODUCT(1*($N2:$W2<=$A$1),P2:Y2) into the formula bar. In this setup, $N2:$W2 represents the range containing your dates, $A$1 is your threshold date, and P2:Y2 represents the corresponding values to sum.

Apply to other rows

Press Enter to calculate the result. Click the bottom-right corner of the cell (fill handle) and drag it down to apply the formula to subsequent rows.

Use the SUMPRODUCT Function with Offset Ranges

Array Dimensions Must Match: Ensure that the two ranges ($N2:$W2 and P2:Y2) have exactly the same number of columns. This matching size is required for SUMPRODUCT to align the criteria and values correctly.

WPS Spreadsheet Alternative

Calculate Complex Array Formulas Easily with WPS Spreadsheet

WPS Spreadsheet offers full support for advanced array formulas like SUMPRODUCT. It perfectly handles staggered data criteria and provides a seamless, fast experience for complex data analysis tasks.

1. Open WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet document.
2. Enter the formula: Select your target cell and input =SUMPRODUCT(1*($N2:$W2<=$A$1),P2:Y2).
3. Fill down the column: Double-click or drag the fill handle to copy the calculation to all necessary rows.
4. Verify results: Check that the calculated totals dynamically update when you change the threshold date in cell A1.

100% compatible with Microsoft Excel formulas and .xlsx formatsRobust processing of advanced array functions like SUMPRODUCTLightweight architecture handles large datasets smoothlyFree to use with a familiar, intuitive user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SUMPRODUCT formula returning a #VALUE! error?

This error usually happens if the sizes of the arrays in the formula do not match (e.g., your date range has 10 columns but your sum range has 11) or if there is text in the ranges being multiplied.

How does the '1*' work in the SUMPRODUCT formula?

The condition ($N2:$W2<=$A$1) generates an array of Boolean TRUE/FALSE values. Multiplying by 1 (1*) coerces these Boolean values into 1s and 0s, which SUMPRODUCT then multiplies against your sum range.

Can I use SUMIFS instead of SUMPRODUCT for every fourth column?

SUMIFS generally requires contiguous ranges and doesn't easily evaluate staggered columns without adding complex helper rows. SUMPRODUCT is much more flexible for array operations across periodic columns.

Excel & Data Problems