WPS Spreadsheets Formula to Add 10 for L Dates Today or Earlier
Question details
The user needs a spreadsheet formula that adds 10 for every cell marked L when its corresponding date is today or earlier.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A date row spans B1:AF1 and a corresponding status row spans B8:AF8. The user wants to count qualifying L entries and assign 10 points to each one.
- Observed behavior
- The original formula does not evaluate the date and status ranges correctly because AND returns one TRUE or FALSE value instead of an array, and mismatched ranges can prevent proper calculation.
Confirm that the date headers and status cells cover the same columns, such as B1:AF1 and B8:AF8. Make sure the date headers contain real spreadsheet dates and the status cells use the exact letter L.
Use COUNTIFS to add 10 for each qualifying L entry
Use this direct formula when the date row and status row are aligned and you want a clear, efficient count of matching cells.
COUNTIFS evaluates both conditions across the specified ranges: the date must be today or earlier, and the corresponding status cell must contain L. Multiplying the count by 10 produces the required total.
In WPS Spreadsheets, click the cell where you want the total to appear.
Type =10*COUNTIFS($B$1:$AF$1,"<="&TODAY(),$B8:$AF8,"L") in the formula bar, then press Enter.
Confirm that each L paired with a date on or before today contributes 10 to the total.
If other rows use the same layout, drag the fill handle down or copy the formula to the required rows. The date range remains fixed while the status row adjusts.
Use SUMPRODUCT for an array-based calculation
Use SUMPRODUCT when you need array-style condition handling or want to extend the calculation with additional criteria.
Calculate Date-Based L Totals in WPS Spreadsheets
WPS Spreadsheets can calculate the total directly with COUNTIFS or SUMPRODUCT, compare dates with TODAY(), and apply the formula across multiple rows while preserving aligned ranges.
- 1. Open your spreadsheet: Launch WPS Office, open the workbook in WPS Spreadsheets, and locate the date and status rows.
- 2. Choose the result cell: Click an empty cell where the total should be calculated.
- 3. Enter the formula: Use =10*COUNTIFS($B$1:$AF$1,"<="&TODAY(),$B8:$AF8,"L") and press Enter.
- 4. Verify the ranges: Check that the date and status ranges have matching dimensions and that the date cells contain valid dates.
- 5. Fill the formula for other rows: Drag the result cell's fill handle down to calculate corresponding totals for additional status rows.
Frequently Asked Questions
Why does AND not work for the date and L conditions?
AND returns one overall TRUE or FALSE value instead of evaluating each column as a separate array item. Use COUNTIFS for range criteria or SUMPRODUCT for array-based multiplication.
What formula counts L entries dated today or earlier?
Enter =10*COUNTIFS($B$1:$AF$1,"<="&TODAY(),$B8:$AF8,"L") in the result cell. The first range checks the date condition and the second checks for L.
Can I use SUMPRODUCT instead of COUNTIFS?
Yes. Enter =SUMPRODUCT(($B$1:$AF$1<=TODAY())*($B8:$AF8="L")*10). This multiplies the two condition arrays and assigns 10 to every matching position.
Why must both ranges have the same dimensions?
Each date must be compared with the status in the same column. For example, B1:AF1 and B8:AF8 both contain 31 cells, so every date aligns with one status cell.
How do I apply the formula to several rows?
Keep the date range absolute with dollar signs, as in $B$1:$AF$1, and leave the status row relative, as in $B8:$AF8. Then drag the formula's fill handle downward.
