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

**Table of Contents**Expand

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

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

### 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)**

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

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

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

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