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.


Download Practice Workbook

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.

xcel Formula If Cell Contains Negative Number


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.

xcel Formula If Cell Contains Negative Number

Read More: How to Count Negative Numbers in Excel (3 Easy Ways)


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: How to Sum Negative and Positive Numbers in Excel


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.

Excel Formula If Cell Contains Negative Number

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.

Excel Formula If Cell Contains Negative Number

Read More: How to Put a Negative Number in Excel Formula (4 Easy Methods)


Similar Readings


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.

Excel Formula If Cell Contains Negative Number

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.

Excel Formula If Cell Contains Negative Number

Read More: How to Show Negative Numbers in Excel (4 Quick Tricks)


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.

Excel Formula If Cell Contains Negative Number

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

Read More: How to Apply Formula for Positive and 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.

Excel Formula If Cell Contains Negative Number

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

Excel Formula If Cell Contains Negative Number

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

Excel Formula If Cell Contains Negative Number

Read More: How to Add Negative Numbers in Excel (4 Easy Ways)


💬 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

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

Leave a reply

ExcelDemy
Logo