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.

Method 1 –  Using the Excel 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.

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

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

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 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

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

• How to Sum If Cell Contains Number and Text in Excel
• How to Use Excel SUMIF with Greater Than Criterion
• How to Use 3D SUMIF for Multiple Worksheets in Excel
• How to Use Excel SUMIF Function Based on Cell Color

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