Formula Errors

How to Count Excel Errors by Date Across Multiple Columns

Maira Mehtab Sep 25, 2026 871 views

Question details

The user needs to count specific error values distributed across several columns, filtered by a specific start and end date range.

How to Count Excel Errors by Date Across Multiple Columns

Product: Spreadsheets
Device & OS: not provided
Scenario: Analyzing spreadsheet data where specific error types must be tallied within a given timeframe without triggering formula errors.
Observed behavior: Using standard COUNTIFS functions returns a #VALUE! error because the single-column date criteria array and the multi-column data array have incompatible dimensions.

Before you start

Ensure your date column contains valid date serial numbers rather than text, and verify that the row counts for your date criteria range and multi-column error data range match exactly.

Solution 1Recommended

Use SUMPRODUCT to Count Errors with Date Criteria

SUMPRODUCT handles multi-dimensional arrays effortlessly, making it the perfect solution to evaluate a single-column date array against a multi-column data array without returning a #VALUE! error.

The COUNTIFS function restricts criteria ranges to the same size and shape. By using SUMPRODUCT, we can multiply boolean arrays of different widths (e.g., a 1-column date range and a 5-column error range) as long as the number of rows is identical.

Set up your criteria cells

Designate specific cells for your variables to keep your formula dynamic. For example, enter your start date in A12, your end date in B12, and the specific error name you want to count (like #N/A) in A14.

Input the SUMPRODUCT formula

Select the cell where you want your total count to appear and type: =SUMPRODUCT(($E$2:$I$9=A14)*($A$2:$A$9>=$A$12)*($A$2:$A$9<=$B$12))

Adjust array references

Update $E$2:$I$9 to reflect the actual multi-column range containing your errors. Modify $A$2:$A$9 to point to your date column. Ensure both ranges cover the exact same rows (e.g., row 2 through 9).

Use SUMPRODUCT to Count Errors with Date Criteria

No Array Shortcut Required: Unlike traditional array formulas, SUMPRODUCT natively handles array calculations without requiring you to press Ctrl+Shift+Enter.

Solution 2

Generate a Unique List of Errors Before Counting

If you are using modern spreadsheet versions, you can automatically extract a list of unique errors present in your dataset before applying your counting logic.

Advanced Data Analysis

Solve Complex Array Formulas with WPS Office

WPS Spreadsheet fully supports advanced array functions like SUMPRODUCT, UNIQUE, and TOCOL. You can seamlessly analyze multi-column datasets and filter by date boundaries exactly as you would in Microsoft Excel.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your file containing the multi-column data and date records.
2. Define criteria ranges: Set up your target start date, end date, and specific error value in blank cells for easy referencing.
3. Apply the SUMPRODUCT function: Use the SUMPRODUCT formula outlined above to calculate accurate totals across mismatched column ranges effortlessly.

100% compatible with Microsoft Excel array formulas and functions.Effortlessly handle large datasets with multi-column dimensions.Free and lightweight suite packed with professional data analysis tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNTIFS return a #VALUE! error for this task?

The COUNTIFS function strictly requires all criteria ranges to have the same size and dimensions. When you attempt to compare a 1-column date array with a 5-column data array, the mismatch causes a #VALUE! error. SUMPRODUCT resolves this by independently multiplying the boolean matrices.

How can I hardcode the error type instead of referencing a cell?

You can insert the error type directly into your SUMPRODUCT formula by wrapping it in quotation marks. For example: =SUMPRODUCT(($E$2:$I$9="#N/A")*($A$2:$A$9>=$A$12)*($A$2:$A$9<=$B$12)).

Why is my SUMPRODUCT formula returning zero?

This commonly happens if the dates in your criteria cells or date column are formatted as text instead of date serial numbers. Select your date cells, change their format to 'Short Date', and ensure there are no hidden spaces.

Can I count multiple different errors at the same time?

Yes. You can add them together by using a plus sign (+) between conditions within the formula, such as (($E$2:$I$9="#N/A")+($E$2:$I$9="#DIV/0!")), wrapping this combined logic within your main SUMPRODUCT multiplier.

Excel & Data Problems