Formula Errors

How to Fix Excel SUMIFS #VALUE! Error with Dates Across Columns

Adam Davis Sep 28, 2026 870 views

Question details

The user encounters a #VALUE! error when attempting to use the SUMIFS function with date criteria spanning horizontally across multiple columns.

Fixing the Excel SUMIFS #VALUE! Error with Dates Across Columns

Product: Excel
Device & OS: not provided
Scenario: Calculating conditional sums across horizontal date columns using the SUMIFS function.
Observed behavior: The formula returns a #VALUE! error because SUMIFS requires the sum range and criteria ranges to have identically matched dimensions.

Before you start

Verify that your SUMIFS formula ranges are the exact same size and shape before proceeding, as unmatched row or column dimensions are the primary cause of this error.

Solution 1Recommended

Restructure Your Table into a Flat Layout

The most reliable way to fix the SUMIFS #VALUE! error is to reshape your data so that dates are in a single column instead of spread across a horizontal multi-column matrix.

The SUMIFS function is strictly designed to work with one-dimensional ranges that exactly match each other in size. When you supply horizontal columns as your sum or criteria range while your other criteria are vertical, Excel cannot align the data arrays, resulting in a #VALUE! error.

Create standard columns

Set up a new table layout with discrete, vertical columns for your data, such as 'Date', 'Project Name', and 'Amount'.

Transfer or Unpivot data

Manually copy your existing data into this vertical format, or use Power Query's 'Unpivot Columns' feature to automatically flatten the horizontal dates into rows.

Apply the standard SUMIFS formula

Rewrite your formula targeting the single columns. For example: =SUMIFS(Table1[Amount], Table1[Date], ">=2024-04-01", Table1[Date], "<2024-05-01").

Restructure Your Table into a Flat Layout

Best Practice: Maintaining a vertical 'flat' data structure not only fixes SUMIFS errors but also makes it much easier to create PivotTables and charts later.

Solution 2

Use SUMPRODUCT as an Alternative Formula

If your workflow requires dates to remain across multiple columns and you cannot restructure the data, bypass the SUMIFS dimension restriction by using the SUMPRODUCT function.

Powerful Spreadsheet Tool

Easily Fix Formula Errors with WPS Spreadsheet

WPS Office offers a robust Spreadsheet application that fully supports advanced array formulas like SUMIFS and SUMPRODUCT. With intuitive error checking and seamless Excel compatibility, you can manage complex data structures effortlessly.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the .xlsx file containing the #VALUE! error.
2. Check formula dimensions natively: Double-click the formula cell to view color-coded highlights of your ranges, making it easy to spot mismatched column or row sizes.
3. Apply SUMPRODUCT or restructure data: Easily rewrite the formula using SUMPRODUCT, or restructure your table using WPS Spreadsheet's intuitive data management tools.

100% compatible with Microsoft Excel formulas and file formats (.xlsx)Advanced formula auditing and error-checking tools to quickly spot #VALUE! triggersLightweight, fast, and free to use across Windows, Mac, and mobile devicesBuilt-in Power Query alternatives and data formatting tools

microsoft office alternative - wps office

Frequently Asked Questions

Why does SUMIFS require ranges of the exact same size?

SUMIFS evaluates arrays cell-by-cell in a strict 1-to-1 relationship. If the criteria range has a different number of rows or columns than the sum range, the function cannot align the logical tests properly and returns a #VALUE! error.

Can I use SUMIF instead of SUMIFS for multi-column date criteria?

No, both SUMIF and SUMIFS suffer from the same limitation regarding array dimensions. They both require the evaluation range and the sum range to share the same size and shape. You should use SUMPRODUCT for multi-column criteria.

Does WPS Office support the SUMPRODUCT function?

Yes, WPS Spreadsheet fully supports SUMPRODUCT, SUMIFS, and hundreds of other standard functions, ensuring complete compatibility with your existing Excel worksheets and complex array formulas.

Excel & Data Problems