Sometimes you may need to use Excel Formula when a cell contains a Negative Number. Thus, I will explain in this article how to use Excel Formula if a cell contains a Negative Number.

**Table of Contents**Expand

## How to Use Excel Formula If Cell Contains Negative Number: 6 Examples

Here, I will demonstrate **6 examples **with detailed steps of how to use **Excel Formula** if a cell contains a **Negative Number**. For your better understanding, I am going to use the following dataset. Which contains **two **columns. Those are **Item,** and** Expense/Income**. The dataset is given below.

### 1. Use of COUNTIF Function If Cell Contains Negative Number

You can use **the** **COUNTIF function** as** Excel Formula** when a cell contains a **Negative Number**. Here, with this formula, you can** count** how many cells contain **Negative Numbers**. The steps are given below.

**Steps:**

- Firstly, you have to select a different cell
**C14**where you want to see the result. - Secondly, you should use the following formula in the
**C14**cell.

`=COUNTIF(C5:C12,"<0")`

**Formula Breakdown**

Here, the **COUNTIF** function will count these cells which will fulfill the given condition.

- Firstly,
**C5:C12**denotes the data range, from where the function should be counting. - Secondly,
**“<0”**denotes the criteria.*Here, when you use any string then you must use the***Inverted Comma.**

- Subsequently, you must press
**ENTER**to get the result.

Finally, you will see** how many negative items** there are.

**Read More: **Excel Formula to Return Zero If Negative Value is Found

### 2. Employing SUMPRODUCT Formula

You can employ **the** **SUMPRODUCT function** as** Excel Formula** when a cell contains a **Negative Number**. Basically, with this formula, you can** count** how many cells contain a **Negative Number**. The steps are given below.

**Steps:**

- Firstly, you have to select a different cell
**C14**where you want to see the result. - Secondly, you should use the formula given below in the
**C14**cell.

`=SUMPRODUCT(--(C5:C12<0))`

- Finally, you must press
**ENTER**to get the result.

Lastly, you will see** how many negative items** there are.

**Formula Breakdown**

Here, the **SUMPRODUCT** function will return the sum of the array which will fulfill the criteria.

- Firstly,
**C5:C12<0**denotes the criteria. These criteria will go through each cell of**C5:C12**and check whether the cell value is**less than 0 or not**. If the cell value is**less than 0**then it will return**TRUE**. Otherwise, it will return**FALSE**.**Output: {TRUE,FALSE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE}**.

- Secondly, –(C5:C12<0) converts the above output into
**Boolean**terms.**Output: {1,0,1,1,1,0,1,1}**.

- Lastly, SUMPRODUCT will return the summation of the above output.
**Output: 6**.

**Read More: **Excel Formula to Return Blank If Cell Value Is Negative

### 3. Applying IF Function If Cell Contains Negative Number

You can apply **the** **IF function** as** Excel Formula** if a cell contains a **Negative Number**. Furthermore, you can modify this function for different types of results according to your preference. The steps are given below.

**Steps:**

- Firstly, you have to select a new cell
**D5**where you want to keep the result. - Secondly, you should use the formula given below in the
**D5**cell.

`=IF(C5<0,C5*-1," ")`

- After that, you must press
**ENTER**to get the result.

**Formula Breakdown**

Here, the **IF** function will return a logical test result.

- Firstly,
**C5<0**denotes the logical test. This test will check whether the cell value of**C5**is**less than 0**or**not**. If. - Secondly,
**C5*-1 —>**when the cell value is**less than 0**then it will do this operation. In this operation, the cell value will multiply with**-1**. - Lastly,
**” ” —>**denotes that when the logic fails then it will return**a blank space**.

- Now, you can drag the
**Fill Handle**icon to**AutoFill**the corresponding data in the rest of the cells**D6:D12**.

Lastly, you will get the following result which shows only **expenses**.

**Read More:** How to Put a Negative Number in Excel Formula

### 4. Use of Conditional Formatting Feature

Here, you can use the **Conditional Formatting** feature as **Excel Formula **if a cell contains a **Negative Number**. Basically, using this formatting you can identify all the **Negative Numbers** at a glance. The steps are given below.

- Firstly, you should select the data range for which you want to apply the
**Conditional Formatting**to format the**Negative numbers.**Here, I have selected the data range**C5:C12**. - Secondly, you need to go to the
**Home**tab.

- Now, from the
**Home**tab >> you must go to the**Conditional Formatting**command. - Then, from the
**Highlight Cells Rules**feature >> you need to choose the**Less Than…**option.

At this time a dialog box named **Less Than** will appear.

- Now, write down the target text
**0**in the**Format cells that are LESS THAN**box. - Then, select the preferred color from the
**drop-down arrow**. Here, I have selected the**Red Text**option. - Finally, press
**OK**to get the changes.

Finally, you will get the **colored text of Expense**.

**Read More: **[Fixed!] Excel Not Adding Negative Numbers Correctly

### 5. Using ABS Function If Cell Contains Negative Number

You can use **the ABS function** as** Excel Formula **to convert the** negative numbers into positive numbers**. Then, you can apply all the Excel formulas to that positive number. The steps are given below.

**Steps:**

- Firstly, you have to select a new cell
**D5**where you want to keep the result. - Secondly, you should use the formula given below in the
**D5**cell.

`=ABS(C5)`

This **ABS** function will return the** positive** value of the cell value of **C5**.

- Finally, press
**ENTER**to get the result.

- After that, you can
**double-click**on the**Fill Handle**icon to**AutoFill**the corresponding data in the rest of the cells**D6:D12**.

Finally, you will get all the **absolute values **of column **C**.

**Read More: **How to Add Negative Numbers in Excel

### 6. Applying Multiplication (*) Operator in Excel Formula

You can apply the **Multiplication (*) **operator as** Excel Formula** when a cell contains a **Negative Number**. Here, with this formula, you can** convert** **Negative Numbers** into** Positive Numbers**. The steps are given below.

**Steps:**

- Firstly, you have to select a different cell
**D5**where you want to see the result. - Secondly, you should use the following formula in the
**D5**cell.

`=C5*-1`

Here, the cell value of **C5** will multiply with **-1**.

- Subsequently, you must press
**ENTER**to get the result.

- After that, you can
**double-click**on the**Fill Handle**icon to**AutoFill**the corresponding data in the rest of the cells**D6:D12**.

At this time, you will get the following result. Here, if you notice deeply then you can see that the **positive** numbers of the **C column** become **negative**. Thus, I will remove all those numbers. *Also, you can apply the formula manually in individual cells which are needed.*

- Firstly, select the cells which have negative values by pressing the
**CTRL**key. Here, I have selected**D6**and**D10**cells.

- Now, press the
**DELETE**key.

Lastly, you will get the following result.

**Read More: **How to Make a Group of Cells Negative in Excel

## 💬 Things to Remember

- In this article, I have described different examples of how to use Excel formulas when a cell contains a
**Negative Number**. So, you should use them according to your requirements.

## Practice Section

Now, you can practice the explained method by yourself.

**Download Practice Workbook**

You can download the practice workbook from here:

## Conclusion

I hope you found this article helpful. Here, I have explained 6 examples to use Excel Formula If a cell contains a Negative Number. Please, drop comments, suggestions, or queries if you have any in the comment section below.

## Related Articles

**<< Go Back to Negative Numbers in Excel | Number Format | Learn Excel**