# Excel Formula If Cell Contains Negative Number (6 Examples)

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.

You can download the practice workbook from here:

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

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

### 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. ### 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. ### 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. ### 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. ## 💬 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. ## 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. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

## Related Articles #### Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts 