How to Use an Excel Formula in WPS Office to Sum Matching Date Rows and Leave Duplicates Blank
Question details
The user needs an Excel formula that compares adjacent rows by date and description, sums the related values on the first matching row, and leaves the following duplicate row blank, with an option to limit the calculation to Income Paid entries.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains dated transaction rows; when two adjacent rows share the same date and description, the user wants their values consolidated into one total instead of repeated totals.
- Observed behavior
- The user needs a formula-driven result where the first row of a matching adjacent pair shows the summed total and the next matching row returns a blank.
Make sure the matching date and description rows are adjacent by sorting or grouping the data first. Check that column A is the date, column B is the description, and columns C and D are the values to sum; adjust the formula references if your layout differs.
Use IF and SUM to Total Matching Adjacent Date and Description Rows
Use this formula when matching rows are arranged next to each other and you only want the total on the first row of each two-row group.
This formula compares the current row with the row above and the row below. It is designed for two consecutive matching rows; groups of three or more require a different approach.
Click the cell in the first data row where the total should appear, for example E2.
Type or paste =IF((A1=A2)*(B1=B2),"",IF((A2=A3)*(B2=B3),SUM(C2:C3,D2:D3),D2+C2*(B2="Income Paid"))) into the selected cell.
Press Enter, then drag the fill handle down through the remaining data rows to apply the formula to each row.
Restrict the Matching-Row Sum to Income Paid Entries Only
Use this version when the worksheet contains multiple description types and only rows labeled "Income Paid" should be included in the matching-row totals.
Use WPS Office Spreadsheets to Sum Matching Date Rows and Remove Duplicate Totals
WPS Office Spreadsheets supports the same IF, SUM, and Boolean formula logic as Microsoft Excel. Enter the formula in your workbook to consolidate matching adjacent date and description rows on the first row and leave the duplicate row blank, all in a free and lightweight office suite.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, open the workbook containing the date, description, and value columns, and click the appropriate worksheet.
- 2. Confirm the formula column layout: Check that column A contains dates, column B contains descriptions, and columns C and D contain the values to sum, or adjust the formula references to match your sheet.
- 3. Enter the formula in the first data row: Select the target cell in row 2 and type or paste the formula =IF((A1=A2)*(B1=B2),"",IF((A2=A3)*(B2=B3),SUM(C2:C3,D2:D3),D2+C2*(B2="Income Paid"))).
- 4. Copy the formula down: Press Enter and drag the fill handle down through all rows that need to be evaluated.
- 5. Verify the consolidated totals: Check that the first matching row displays the combined total and the following duplicate row remains blank.
Frequently Asked Questions
Why does the formula leave the duplicate row blank?
The first IF compares the current row with the row above. When the date and description match, it returns "", so the duplicate row is blank. The second IF detects when the current row matches the row below and returns the combined total on the first row of the pair.
How do I limit the calculation to Income Paid rows only?
Add description checks to both IF tests. Use =IF((A1=A2)*(B1=B2)*(B1="Income Paid"),"",IF((A2=A3)*(B2=B3)*(B2="Income Paid"),SUM(C2:C3,D2:D3),D2+C2*(B2="Income Paid"))).
Does this formula work for three or more consecutive matching rows?
No. The accepted formula is designed for two consecutive matching rows. For groups of three or more, use a helper column or a different running-total approach so each group is consolidated correctly.
Do I need to sort the data before using the formula?
Yes. The formula compares adjacent rows, so matching date and description rows must be next to each other. Sort or group the data by date and description before copying the formula down.
Can I use this formula in WPS Spreadsheets exactly like in Microsoft Excel?
Yes. WPS Spreadsheets supports the same formula syntax and functions, including IF, SUM, and Boolean expressions, and it opens and saves .xlsx files without changing the formulas.
