Function Problems

How to Find and Sum Values Below Zero in Excel

Huda Qurayshi Oct 14, 2026 871 views

Question details

The user wants to return all sales prices and order numbers associated with negative quantities and calculate their total sum.

How to Find and Sum Values Below Zero in Excel

Product: Excel
Device & OS: not provided
Scenario: Auditing sales or inventory data where the user needs to isolate records containing negative quantities and calculate the financial impact.
Observed behavior: The user needs to extract multiple matching records and calculate totals based on a below-zero condition, recognizing that single-match functions like XLOOKUP are insufficient for this goal.

Before you start

Ensure your data is organized into clearly defined columns (e.g., Quantities in Column A, Prices in Column B, Order Numbers in Column C) and that you are using a version of Excel or WPS Spreadsheet that supports dynamic arrays.

Solution 1Recommended

Use the FILTER Function to Find All Matching Records

The FILTER function is the ideal choice for extracting an array of all items that meet a specific condition, whereas XLOOKUP will only return the first match.

To return multiple values, such as all sales prices or order numbers linked to negative quantities, the dynamic FILTER function allows you to pull all matching data at once.

Select the destination cell

Click on an empty cell where you want the list of matching sales prices to begin.

Enter the FILTER formula for prices

Type the formula =FILTER(B2:B10, A2:A10<0) (assuming Column A contains quantities and Column B contains prices) and press Enter.

Extract order numbers

To retrieve the corresponding order numbers instead, select another blank cell and type =FILTER(C2:C10, A2:A10<0), then press Enter.

Use the FILTER Function to Find All Matching Records

Dynamic Spill Range: The FILTER function will automatically spill the results into adjacent cells below. Make sure there is enough empty space so you do not get a #SPILL! error.

Solution 2

Use SUMIF to Calculate the Total of Values Below Zero

If you only need the aggregate sum of sales prices where the associated quantity is negative, SUMIF provides a quick and direct calculation.

Solve Data Problems Easily

Easily Filter and Sum Data with WPS Spreadsheet

WPS Office provides powerful dynamic array functions like FILTER and essential math functions like SUMIF, allowing you to manage inventory and analyze complex sales data effortlessly.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your sales and quantity data.
2. Apply your formula: Select a blank cell and type your =FILTER or =SUMIF formula based on your columns.
3. View your dynamic results: Press Enter to instantly view your spilled array of negative records or calculated total.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Supports advanced dynamic array functions for data extraction.Lightweight, fast, and completely free to use for personal productivity.

microsoft office alternative - wps office

Frequently Asked Questions

Why does XLOOKUP only return one order number for negative values?

XLOOKUP is designed to find and return only the first matching record it encounters in an array. To return all records that meet a criteria, such as all quantities below zero, you must use the FILTER function instead.

Can I filter and sum values below zero at the same time using one formula?

Yes. You can wrap the FILTER function inside a SUM function, such as =SUM(FILTER(B2:B10, A2:A10<0)). This yields the exact same calculated result as using the SUMIF function.

What if the FILTER function returns a #CALC! error?

The #CALC! error usually occurs when no data matches the criteria (i.e., there are no quantities below zero in your range). You can prevent this by adding the optional 'if_empty' argument: =FILTER(B2:B10, A2:A10<0, "No negative values").

Excel & Data Problems