How to Use SUMIF to SUM Less Than 0 in Excel

The dataset represents the profit margin data of a super shop for 2021 and 2022 in Florida and Georgia.

Dataset containing Year, Region, Item, Profit(USD)


Method 1 –  Using the Excel SUMIF Function

defining values using the SUMIF function

Using the SUMIF function with a defined value, you can sum values less than 0.

Using the following formula, we measured a total loss of $32,615 in two years on particular items in cell H4.

=SUMIF(E5:E16, “<” & 0)

Note: you can also insert any other value instead of 0 to add specific values greater or less than.

Read More: How to Sum If Cell Contains Number in Excel

Method 2 – Using Cell Reference in Applying the SUMIF Formula

Using cell reference while using the SUMIF function

Unlike defining specific values, using cell references can be another way to find a total of values less than 0.

Entering the following formula, which contains the SUMIF function, we get a total loss of $32,615 in cell H5.


Read More: Sum If Greater Than and Less Than Cell Value in Excel

Method 3 – Using Named Range to Sum Values Less Than 0

Using Named Range

Converting the entire range into a Named Range and using the SUMIF formula, we get a total of $32,615 loss in cell H5.

  • Rename the E5:E16 as Profit_USD.
  • The range= Profit_USD and criteria = H4 = <0 in the SUMIF function formula.

Method 4 – Using the Excel SUMIFS Formula to Sum a Total of Less Than 0

Using the SUMIFS function

Using the SUMIFS function you are allowed to apply multiple criteria.

Applying the formula below, we measure a $15,565 loss for T-Shirt items in two years.

  • Find out H5 = <0 in the E5:E16 range.
  • Get the data for H4 = T-shirt from the D5:D16 range.
  • Matching the above two criteria, we get a $15,565 loss for the T-shirt in the H4 cell.

How to Use Excel SUMIF to Sum Values Greater Than 0

sum up values greater than 0

Not only less than 0, but also one can sum values greater than 0 using the SUMIF function. Using the formula below, we get a profit of $455,825 in the H5 cell over two years.


Download the Practice Workbook

To practice, download the Excel Workbook file from the link below.

Related Articles

<< Go Back to Excel SUMIF Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
MD Tanvir Rahman
MD Tanvir Rahman

MD Tanvir Rahman, BUET graduate in Naval Architecture and Marine Engineering, dedicated over a year to the ExcelDemy project. He is an Excel and VBA Content Developer. Having authored 50+ insightful articles, he actively updates and improves over 80 articles, reflecting his commitment to accuracy and currency, managing day-to-day operations, and analyzing and developing Excel and VBA tutorials. His broad interests encompass Data Analysis, Advanced Excel, VBA Macro, Excel Templates, Excel Automation, and Excel Power Query, showcasing a... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF