How to Use an Excel Formula to Return Values When Date and Text Match in WPS Spreadsheets
Question details
The user needs an Excel-compatible formula that checks whether a date is in January and a text cell equals "loon", then returns the matching number from another column or leaves the row blank, with a SUMPRODUCT alternative for the total.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a data range containing dates, descriptions, and numbers and wants to extract or total values when both a January date and the text "loon" match on the same row.
- Observed behavior
- The goal state is for matching rows to show the value from column C in column D and for nonmatching rows to remain blank; a single-cell SUMPRODUCT total should also calculate the sum of all matching values.
Make sure your worksheet has dates in column A, text descriptions in column B, and numeric values in column C. Confirm that the date cells are formatted as actual dates rather than text so MONTH(A2) can read them correctly.
Return Matching Values Row by Row with IF, AND, and MONTH
Use this formula in D2 and copy it down when you need a visible value for each matching row and a blank for nonmatching rows.
The formula combines MONTH(A2)=1 to identify January dates, B2="loon" to check the exact text, and IF to return the value from C2 only when both conditions are true. If either condition is false, the formula returns an empty string so the row remains blank.
Click cell D2 in your WPS Spreadsheets worksheet, where the first result should appear.
Type =IF(AND(MONTH(A2)=1,B2="loon"),C2,"") and press Enter. WPS Spreadsheets accepts this Excel-compatible formula directly.
Drag the fill handle from D2 down through the remaining data rows to apply the same date and text check to each row.
Return or Total a Whole Range with Dynamic Array and SUMPRODUCT
Use this approach when you want one formula to spill values for multiple rows or calculate the total of matching rows in a single cell.
Use WPS Spreadsheets to Return Values When a Date and Text Match
You can complete this task directly in WPS Spreadsheets using the same Excel-compatible IF, AND, MONTH, and SUMPRODUCT formulas. It keeps row formulas readable while giving you a single-cell total, without needing Microsoft Excel.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the workbook that contains the date, text, and value columns, and click the worksheet tab where you want the results.
- 2. Enter the row formula: Click the first result cell, type =IF(AND(MONTH(A2)=1,B2="loon"),C2,""), and press Enter.
- 3. Fill the formula down: Drag the fill handle from the first result cell down through all data rows to apply the check to each row.
- 4. Add the total formula: Click an empty cell and type =SUMPRODUCT((MONTH(A2:A60)=1)*(B2:B60="loon")*C2:C60), then press Enter to get the matching total.
Frequently Asked Questions
What does MONTH(A2)=1 do in the formula?
It converts the date in A2 to its month number and checks whether it equals 1, meaning January. If A2 contains text instead of a real date, the formula may return an error, so confirm the cell is formatted as Date.
How can I match a different text instead of "loon"?
Replace B2="loon" with your own exact-text condition, such as B2="invoice". Keep the text inside double quotes. For partial matching, use SEARCH or ISNUMBER instead of direct equality.
Why are nonmatching rows blank?
The IF function returns an empty string "" when the AND condition is false. If you prefer a zero or another value to appear, replace "" in the formula with 0 or your custom text.
Can I total all matching values without creating a helper column?
Yes. Use =SUMPRODUCT((MONTH(A2:A60)=1)*(B2:B60="loon")*C2:C60) in one cell to sum only the rows where the January date and exact text both match.
Does this work the same in WPS Spreadsheets and Microsoft Excel?
Yes, these formulas use MONTH, AND, IF, and SUMPRODUCT and are compatible across WPS Spreadsheets and Excel. The results are the same when the date and text data are consistent.
