Formula Errors

How to Count Y Values Between Date Columns with Text Headers in Excel

Tauseeq Magsi Oct 22, 2026 869 views

Question details

The user needs a formula to count specific values (like "Y") in an attendance table within a dynamic date range, but the standard formulas fail because the table's date headers are evaluated as text.

How to Count Values Between Date Columns When Excel Table Headers Become Text

Product: Excel
Device & OS: not provided
Scenario: Calculating total attendance ("Y" marks) between a Start Date and End Date across multiple columns, where converting the data into an official Excel Table has forced the date headers into a text format.
Observed behavior: The COUNTIFS formula returns an error or zero because it cannot perform greater-than or less-than date comparisons on table headers that are stored as text rather than actual date values.

Before you start

Ensure that your Start Date and End Date reference cells contain valid date formats, and identify the exact range of your text-formatted date headers.

Solution 1Recommended

Use SUMPRODUCT and DATEVALUE to Evaluate Text Headers

This solution converts the text-based date headers back into readable dates within the formula calculation, allowing for accurate logical comparisons and counting.

When data is converted to an Excel Table, headers are strictly treated as text. To perform date math, the DATEVALUE function forces these text strings into serial numbers that the SUMPRODUCT function can evaluate against your Start Date and End Date.

Set up your criteria references

Define two specific cells in your worksheet to hold the dynamic date range, for example, name them 'Start_Date' and 'End_Date'.

Select the target total cell

Click on the first cell in your 'Y Tot' (Totals) column where you want the calculated attendance count to appear.

Input the SUMPRODUCT formula

Enter the following formula: =SUMPRODUCT((DATEVALUE($F$4:$Q$4)>=Start_Date)*(DATEVALUE($F$4:$Q$4)<=End_Date)*(F5:Q5="Y")). Replace $F$4:$Q$4 with your actual header row range, and F5:Q5 with the corresponding data row.

Apply formula to the entire column

Press Enter to execute the formula, then drag the fill handle down to copy the formula for all the subsequent rows in your table.

Use SUMPRODUCT and DATEVALUE to Evaluate Text Headers

Using Structured References: If you are utilizing an official Excel Table, you can make the formula dynamic by replacing the absolute header reference ($F$4:$Q$4) with structured references like Table1[#Headers].

Master Complex Formulas Easily with WPS Spreadsheet

WPS Spreadsheet provides robust formula calculation capabilities, easily handling array functions like SUMPRODUCT and DATEVALUE to resolve table header formatting issues.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your attendance tracking workbook.
2. Locate your totals column: Select the cell where you wish to display the count of 'Y' values between your specific dates.
3. Apply the SUMPRODUCT formula: Type the formula =SUMPRODUCT((DATEVALUE($F$4:$Q$4)>=Start_Date)*(DATEVALUE($F$4:$Q$4)<=End_Date)*(F5:Q5="Y")) adjusting the cell references to match your table.
4. Calculate and fill: Press Enter to view the result, then use the smart fill handle to drag and calculate the remaining rows instantly.

Fully compatible with Microsoft Excel formulas and structured table referencesAdvanced data analysis and dynamic array calculation engineLightweight software for smooth performance on large attendance tablesIntuitive error checking and formula auditing tools

microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNTIFS fail on Excel Table date headers?

When you format a data range as an official Table in Excel, it automatically converts all header cells to text format. COUNTIFS relies on actual numeric date values to perform logical comparisons (like greater than or less than), which causes it to fail when encountering text.

What is the role of DATEVALUE in this formula?

The DATEVALUE function takes a date stored as text and converts it into a serial number. This enables the SUMPRODUCT formula to mathematically compare the text headers against your actual Start Date and End Date values.

How do I dynamically update columns when new dates are added?

Instead of using absolute cell ranges like $F$4:$Q$4, you can use structured table references such as TableName[#Headers]. When new date columns are added to the table, the formula will automatically expand to include them in the calculation.

Excel & Data Problems