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.
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*-1Here, 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
Get FREE Advanced Excel Exercises with Solutions!
