Formula Errors

How to Fix SUMPRODUCT Array Formula for Total Days in Date Ranges

Muhammad Talha Sep 30, 2026 869 views

Question details

Calculate the total number of days covered by multiple date ranges in a specific month, while properly handling rows that have blank end dates.

Fix SUMPRODUCT Array Formula for Total Days in Date Ranges

Product: Spreadsheet
Device & OS: not provided
Scenario: Calculating the total days covered by start and end dates in March 2024 across multiple rows, where some end dates are intentionally left blank.
Observed behavior: Formulas using MIN or MAX fail to calculate properly because those functions return a single aggregated value instead of producing a row-by-row array needed for summing multiple ranges.

Before you start

Ensure your start dates (e.g., Column A) and end dates (e.g., Column B) are formatted as Dates rather than Text, and confirm the specific cell range your data occupies.

Solution 1Recommended

Use SUM and IF to Calculate Total Days

Replace the MIN/MAX logic with an IF function inside a SUM array formula to evaluate row-by-row date differences dynamically.

Because MIN and MAX evaluate an entire range and return just one value, they cannot be used to generate an array of row-by-row results. Instead, you need a formula that evaluates each row individually.

By utilizing the IF function, you can identify blank end date cells and dynamically substitute them with a default end date (like March 31, 2024) before calculating the date difference.

Select the result cell

Click on the specific cell where you want the total combined days for all date ranges to be displayed.

Enter the array formula

Type the formula: =SUM(IF(B2:B7="",DATE(2024,3,31),B2:B7)-A2:A7+1). Modify the ranges A2:A7 and B2:B7 to match your actual start and end date columns.

Apply as an array calculation

Instead of simply pressing Enter, press Ctrl + Shift + Enter on your keyboard. This confirms the calculation as an array formula, applying the logic to every single row in the specified range.

Understanding the Formula: The formula checks if cells in B2:B7 are blank. If true, it uses DATE(2024,3,31); otherwise, it uses the actual date in column B. It then subtracts the start dates in column A, adds 1 to make the count inclusive, and sums the entire array.

Data Calculation Solution

Easily Manage Complex Array Formulas in WPS Spreadsheet

WPS Spreadsheet offers robust support for array formulas, date calculations, and conditional logic, making it simple to process complex datasets and troubleshoot formula errors.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your date ranges.
2. Input the calculation: Select the total cell and input your =SUM(IF(...)) formula directly into the formula bar.
3. Calculate the array: Press Ctrl+Shift+Enter to accurately evaluate the row-by-row dates and retrieve your total.

Fully compatible with Microsoft Excel array formulas and functionsBuilt-in dynamic array capabilities for seamless data evaluationIntuitive formula auditing tools to quickly spot and fix calculation errorsFree and lightweight office suite for comprehensive data management

Frequently Asked Questions

Why does using MIN or MAX in my array formula produce the wrong total?

MIN and MAX functions are designed to look at an entire selected range and return a single minimum or maximum value. When calculating differences across multiple rows in an array formula, returning a single value breaks the row-by-row pairing. You must use logic like an IF statement to return an array of values instead.

How can I automatically fill in today's date if the end date is blank?

You can replace the fixed DATE() function in the formula with the TODAY() function. The formula would look like: =SUM(IF(B2:B7="",TODAY(),B2:B7)-A2:A7+1).

Why do I need to add 1 at the end of the date difference formula?

Subtracting a start date from an end date calculates the mathematical difference between the two points in time. Adding 1 ensures the count is inclusive, meaning both the start day and the end day are counted as full days.

What happens if I forget to press Ctrl+Shift+Enter?

In older spreadsheet versions without dynamic array support, pressing just Enter instead of Ctrl+Shift+Enter will cause the formula to return a #VALUE! error or an incorrect result, because it attempts to process an array operation as a standard single-cell calculation.

Excel & Data Problems