Function Problems

How to Match Positive and Negative Values by Group in Excel

Ayan Masood Oct 21, 2026 868 views

Question details

The user needs to offset matching positive and negative amounts within specific data groups and retain only the rows that are unbalanced.

How to Match Positive and Negative Values by Group in Excel

Product: Excel
Device & OS: not provided
Scenario: Reconciling financial data or account statements where offsetting transactions within the same group must be identified and eliminated.
Observed behavior: Positive and negative amounts within each group are successfully offset against each other, leaving only the unbalanced or outstanding rows visible.

Before you start

Ensure your dataset is sorted by the group identifier column (e.g., Column A) to make it easier to visually verify the matching results.

Solution 1Recommended

Use an Array Formula to Identify Unbalanced Rows

Apply a combination of FILTER, INDEX, and COUNTIFS functions to dynamically find matching offsetting amounts based on grouped data.

This method utilizes dynamic array functions to check if each positive value has a corresponding negative value within the same group. It assigns a running count to handle duplicate amounts appropriately.

Prepare the helper column

Create a new column next to your data (e.g., Column D) and name it 'Offset Status'.

Enter the offsetting formula

Assuming your group identifier is in Column A and your amounts are in Column C, select cell D2 and enter the following formula: =IF(C2>0,IFERROR(INDEX(FILTER($C$2:$C$14,(($A$2:$A$14=A2)*($C$2:$C$14<0)*($C$2:$C$14=-C2))),COUNTIFS(A$2:A2,A2,C$2:C2,C2),1),"No offsetting amount"),"")

Apply to all rows

Press Enter, then drag the fill handle down from cell D2 to apply this formula to the rest of the rows in your dataset.

Filter the unbalanced rows

Select the header row, go to the 'Data' tab, click 'Filter', and filter the 'Offset Status' column to display only the rows marked 'No offsetting amount'.

Use an Array Formula to Identify Unbalanced Rows

Dynamic Array Compatibility: The FILTER function is available in newer versions of spreadsheet software. This formula safely isolates unmatched positive values.

Solution 2

Use Power Query to Group and Offset Values

Create a grouped query using the absolute value of the amount to systematically compare positive and negative counts.

Efficient Data Processing with WPS Spreadsheet

Easily Reconcile Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER and COUNTIFS, allowing you to instantly match and offset positive and negative values. It is a highly compatible and lightweight tool for complex data analysis.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing the financial or grouped data you want to reconcile.
2. Add a helper column: Create a new column adjacent to your transaction amounts to hold the offset status.
3. Insert the array formula: Paste the provided nested IF and FILTER formula into the first cell of the helper column and drag it down.
4. Isolate the unbalanced rows: Use the AutoFilter tool in the Data tab of WPS Spreadsheet to show only the unmatched, outstanding transactions.

Fully compatible with Microsoft Excel formulas and functionsSupports dynamic arrays like FILTER for complex data reconciliationLightweight and fast, ensuring smooth performance even with large datasetsFree to use for everyday office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does the FILTER function return a #NAME? error?

The FILTER function is a dynamic array function introduced in newer spreadsheet software versions. If you are using an older version, the software will not recognize it and will return a #NAME? error. You can resolve this by using WPS Office or upgrading your software.

What happens if there are multiple identical positive amounts but only one negative amount?

The formula handles this by using a COUNTIFS function with an expanding range (e.g., C$2:C2). It assigns a sequential number to each duplicate value, ensuring that one negative amount will only offset exactly one corresponding positive amount. The remaining identical positive amounts will correctly show as 'No offsetting amount'.

Can I use Conditional Formatting instead of a helper column?

Yes. You can create a Conditional Formatting rule using COUNTIFS to check if the count of a specific positive amount within a group matches the count of its negative counterpart. If the counts do not match, you can set the rule to highlight the unmatched cells.

Excel & Data Problems