How to Use SUMIF to SUM Less Than 0 in Excel (3 Easy Ways)

In this article, we will illustrate how to use the Excel SUMIF function to sum values less than 0.

Profit and loss are highly related to business. In Some days businessmen taste profit whereas sooner or later loss takes place. Not only in business but also in research or data science, sometimes we need to measure the sum of negative values.

As you can observe in the overview image, we apply 3 approaches such as defining value, cell reference, and named range while using the SUMIF function formula to find out the total of the values less than 0. Later you will explore the use of the Excel SUMIFS function, to sum up values less than zero (0).

Overview of SUMIF Function

SUMIF is a function that sums up values based on Criteria. If you are likely to sum up some values based on a specific value, the Excel SUMIF function can be your key.

=SUMIF(Range,Criteria)

Range: Range is the array, where you want to apply criteria.

Criteria: Criteria is the condition that allows adding from the range.

As you can see in the image, we illustrated criteria and range while using the SUMIF function.

SUMIF Less Than 0 in Excel: 3 Approaches to SUM

We determine to use the above dataset throughout this article while constructing several methods to sum up values less than 0 using the Excel SUMIF function. The dataset represents the Profit margin data of a super shop for 2021 and 2022 in Florida and Georgia state.

1. Define Value to Sum Values Less Than 0 Using Excel SUMIF Function

Using the SUMIF function with a defined value, you will be able to sum values less than 0. Using the following formula, we measured a total loss of $32,615 in two years on particular items in the H4 cell.

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

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

2. Use Cell Reference Applying SUMIF Formula to Sum Values Less Than 0

Unlike defining specific values, the use of cell reference can be another way to find a total of values less than 0. Implementing the following formula containing the SUMIF function, we obtain a total loss of $32,615 in the H5 cell.

=SUMIF(E5:E16,H4)

3. Use Named Range to Sum Values Less Than 0

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

=SUMIF(Profit_USD,H4)

To begin the method, we rename the E5:E16 as Profit_USD first.
After that, the range= Profit_USD and criteria = H4 = <0 in the SUMIF function formula.

Use Excel SUMIFS Formula to Compute Total 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.

=SUMIFS(E5:E16,E5:E16,H5,D5:D16,H4)

First, we find out H5 = <0 in the E5:E16 range.
Then, get the data for H4 = T-shirt from the D5:D16 range.
Matching the above two criteria, we get a total of $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 obtain $455,825 profit in the H5 cell through two years.

=SUMIF(E5:E16,H4)

Frequently Asked Questions

1. How do you use less than in Sumif?

Let’s say we want to sum up values less than 10 from the A1:A10 range. The formula is, =SUMIF(A1:A10, “<=” & 10). You can also use cell reference by inputting B1 = <10. The formula is,

=SUMIF(A1:A10, B1)

2. How do you use <= in Sumif?

You can use the =SUMIF(range, “<=” & value) formula while working with the SUMIF function. range=, where you want to apply criteria and sum up based on that criteria, value= specific numbers, text, or characters in the criteria.

3. What is the Sumif <> 0 in Excel?

Symbol <> represents not equal to. During the use of the =SUMIF(A1:A10, “<>” & 0) formula, the SUMIF function excludes the values containing 0 and sums up the rest of the A1:A10 range. In contrast, particularly the use of the Greater than (>) and Less than (<) symbols dictate the sum of all the positive and negative values respectively.

4. What is the difference between Sumif and Sumifs?

Excel SUMIF function allows a single criterion in a formula whereas the SUMIFS function works with multiple criteria. General syntax of SUMIF function, =SUMIF(range,criteria). On the other hand, the SUMIFS function is, =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2],…).

📝 Takeaways from This Article

📌 Defining the specific value while using the Excel SUMIF function.

📌 Applying cell references during the use of the SUMIF formula.

📌 Using Named Range in the SUMIF function.

📌 Application of SUMIFS function to sum up based on multiple criteria.

Download Practice Workbook

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

Sum values less than 0.xlsx

Conclusion

We briefly expound on the use of the Excel SUMIF function to sum values less than 0. I hope you enjoyed your learning and will be able, to sum up, values less than 0 in Excel. Any suggestions including queries are appreciated. Don’t hesitate to leave your thoughts in the comment section. For more Learning, explore our site.

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

Get FREE Advanced Excel Exercises with Solutions!