VBA & Macro Problems

How to Find Numbers in Excel That Add Up to Zero

Chanuka Geekiyanage Oct 1, 2026 869 views

Question details

The user needs to identify a specific combination of numerical values within a given Excel list that perfectly totals exactly zero.

How to Find Numbers in Excel That Add Up to Zero

Product: Microsoft Excel
Device & OS: not provided
Scenario: Reconciling accounting records, auditing financial data, or matching positive and negative values to locate exact offsets in a dataset.
Observed behavior: The user wants to automate the process of finding subset combinations that equate to zero instead of manually guessing the values.

Before you start

Ensure your dataset is cleaned, organized in a single column without empty rows, and contains a mix of both positive and negative numbers. If dealing with sensitive financial data, strongly avoid third-party online tools.

Solution 1Recommended

Use the Excel Solver Add-in

Excel Solver is the most secure and reliable built-in method for finding combinations that sum to a specific target, like zero, by using binary variables.

Solver uses an iterative algorithm to find optimal solutions based on constraints. By pairing your data with a column of binary variables (0 or 1), Solver can identify exactly which numbers to include to hit your target sum.

Enable the Solver Add-in

Go to File > Options > Add-ins. In the Manage drop-down at the bottom, select 'Excel Add-ins' and click Go. Check the box for 'Solver Add-in' and click OK.

Set up your dataset

Place your list of numbers in column A (e.g., A2:A20). In column B (B2:B20), type the number 0 next to each value. These will act as the binary selection variables.

Create a SUMPRODUCT formula

In cell C2, enter the formula =SUMPRODUCT(A2:A20, B2:B20). This cell calculates the total sum of the numbers multiplied by their adjacent binary value.

Configure the Solver parameters

Navigate to the Data tab and click Solver. Set your 'Objective' to cell C2, and choose the 'Value of' option, typing 0 in the box.

Set variable cells and constraints

In the 'By Changing Variable Cells' box, select B2:B20. Click 'Add' to create a constraint, select B2:B20, choose 'bin' (binary) from the middle dropdown, and click OK. Click Solve.

Identify the Matching Values: Once Solver finishes, the cells in column B that changed from 0 to 1 indicate the specific numbers in column A that sum to exactly zero.

Solution 2

Automate with a Custom VBA Macro

For lists of around 12-20 items, a custom VBA macro can rapidly iterate through every possible combination to find exact or nearest matches.

Solution 3

Use an Online Combination Calculator

A fast alternative for non-sensitive data, utilizing third-party web tools to calculate target sums without requiring Excel formulas.

Powerful Data Analysis

Find Target Sums Easily in WPS Spreadsheet

WPS Spreadsheet is equipped with powerful built-in data analysis tools, including Solver capabilities and full VBA macro support, allowing you to instantly identify numbers that sum to zero with ease.

1. Open your dataset: Launch WPS Spreadsheet and open your .xlsx file containing the target numbers.
2. Access the Data tab: Navigate to the Data tab on the top ribbon and locate the advanced analysis tools like Solver or Goal Seek.
3. Set your target variables: Configure your binary variable columns and define your target objective cell to calculate a sum of 0.
4. Run the calculation: Click solve to instantly reveal which exact rows perfectly offset each other.

Seamlessly compatible with Microsoft Excel (.xlsx) file formatsBuilt-in Solver and Goal Seek tools for advanced calculationsFull support for VBA macros to automate complex permutationsLightweight, fast, and completely free to use

Frequently Asked Questions

Is there a limit to how many numbers Excel Solver can process?

Yes, the standard Solver add-in in Excel supports a maximum of 200 variable cells. If your dataset contains more than 200 numbers, you will need to break the data into smaller chunks or use a premium data analysis add-in.

Why doesn't the VBA macro find a combination that perfectly matches zero?

If there is no mathematical combination of the provided numbers that exactly totals zero, the macro will fail. Advanced macros can be programmed to track the minimum absolute difference during iterations to return the combination nearest to zero instead.

Can I use Goal Seek instead of Solver to find numbers that add to zero?

No. Goal Seek is designed to change only a single variable input to reach a specific target. Finding combinations from a list requires changing multiple binary variables simultaneously, which is exclusively the function of the Solver tool.

Why did my computer freeze while running a combination macro?

Testing all possible combinations increases exponentially with each added number. For a list of 30 numbers, there are over 1 billion combinations. Attempting to run a VBA permutation macro on large lists will exhaust system memory and freeze the application.

Excel & Data Problems