Formula Errors

Excel Formula for Multiple Driver Criteria and Date Ranges

Maira Mehtab Sep 21, 2026 868 views

Question details

Calculate total hours for a specific driver within a July 1 to June 30 date range, where the driver's name might appear in any of four different unit columns.

Product: Excel
Device & OS: not provided
Scenario: Summing values based on a date range and an OR condition across multiple driver columns (Unit 1 to Unit 4) where some cells may be blank.
Observed behavior: A standard SUMIFS formula fails because it applies strict AND logic, which cannot evaluate multiple column criteria where a match in any one of the four columns is acceptable.

Before you start

Ensure your dataset is organized consistently, preferably as an Excel Table, so that absolute references cover your entire data range without missing newly added rows.

Solution 1Recommended

Use SUMPRODUCT to Evaluate Multiple OR Criteria

SUMPRODUCT is the most reliable function for evaluating multiple criteria across different columns simultaneously without requiring complex array entry combinations.

SUMIFS requires all criteria to be true (AND logic). When searching for a driver's name across four different columns (Unit 1 to Unit 4), you need OR logic. SUMPRODUCT allows you to add conditions together to simulate this OR logic while keeping your date parameters as AND logic.

Identify your data ranges

Determine the column ranges for your criteria. For example: Dates in A2:A100, Hours in B2:B100, and Unit Drivers in C2:F100.

Select the target cell

Click on the empty cell where you want the calculated total hours to appear.

Enter the SUMPRODUCT formula

Input the formula: =SUMPRODUCT((A2:A100>=DATE(2023,7,1))*(A2:A100<=DATE(2024,6,30))*((C2:C100="DriverName")+(D2:D100="DriverName")+(E2:E100="DriverName")+(F2:F100="DriverName")>0)*B2:B100)

Execute the formula

Press Enter. The formula will multiply the rows matching the date range and containing the driver's name by the corresponding hours and sum them.

Cell Referencing: Instead of typing "DriverName" manually, you can replace it with a cell reference (e.g., $H$1) that contains the driver's name to make your formula dynamic.

Solution 2

Use FILTER and SUM for Microsoft 365 Users

If you are using Excel 365, you can use modern dynamic array formulas to filter the relevant hours before summing them.

Master Advanced Spreadsheets

Calculate Complex Criteria Seamlessly with WPS Office

WPS Spreadsheet fully supports advanced array functions like SUMPRODUCT and dynamic arrays like FILTER. You can effortlessly manage complicated date ranges and multi-column criteria with its intuitive interface and built-in formula checking tools.

1. Open your data file: Launch WPS Spreadsheet and open your workbook containing the driver data.
2. Access the Formulas tab: Navigate to the Formulas tab on the top ribbon and click on 'Insert Function'.
3. Select SUMPRODUCT or FILTER: Search for your preferred function and use the visual formula builder to input your Date, Hours, and Driver ranges.
4. Calculate and verify: Click OK to apply the formula and verify your total hours are calculated correctly.

100% compatibility with Microsoft Excel formulas and syntaxBuilt-in function library with visual syntax helpersFree, lightweight, and fast spreadsheet processingCross-platform support for Windows, Mac, iOS, and Android

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't SUMIFS work for multiple columns?

SUMIFS strictly uses AND logic across all the ranges you provide. If you specify four different columns for the driver name, the formula demands that the driver's name is present in all four columns of a single row at the same time, which is not the case when you only need them to appear in any one of the units.

How does the formula handle blank cells in the driver columns?

In logical spreadsheet functions, a blank cell evaluates to a zero or FALSE condition. When evaluating whether a blank cell equals "DriverName", the result is 0. This does not disrupt the SUMPRODUCT formula, and blank cells simply won't contribute to triggering the sum.

Can I use text strings for dates instead of the DATE function?

Yes, but you must force Excel to recognize the text as a date value. You can do this by adding +0 or -- to the text string, such as (A2:A100>="2023-07-01"+0). However, using the DATE function is recommended as it prevents errors caused by regional date format settings.

Excel & Data Problems