Bishawajit Chakraborty
In Excel, you frequently need to conditionally add the data when working with numbers. And utilizing Excel’s built-in formulas, this is simple to accomplish. In this article, we will apply the Excel formula for positive and negative numbers.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
4 Easy Ways to Apply Formula for Positive and Negative Numbers in Excel
In all the following methods, you will learn how to apply formulas for positive and negative numbers in Excel using the SUMIF function, applying the IF function, utilizing the COUNTIF function, and applying a VBA Code. Let’s suppose we have a sample data set.
1. Using SUMIF Function for Summing Positive and Negative Numbers
In a worksheet, it is typically simple for us to add up a variety of data, but in this case, I only want to add up positive or negative numbers. In this first step, we will use the SUMIF function to add positive and negative numbers in Excel.
Step 1:
- At the start of this section, we will create a sample data set for summing up positive and negative numbers.
Step 2:
- Firstly, select cell D13.
- Secondly, select column F.
- Thirdly, write down the following formula.
=SUMIF(F5:F11,">0")
Step 3:
- Besides, press ENTER, and the result will show the sum of all the positive numbers in cell D13.
- Finally, use the Fill Handle tool and drag it down from cell D13 to D14.
Step 4:
- Therefore, we will get the sum of all the negative numbers in cell D14.
Read More: How to Change Positive Numbers to Negative in Excel (6 Ways)
2. Applying IF Function for Identifying Positive and Negative Numbers
The simplest method is to use the IF function, although there are several methods to accomplish it with special functions. Typically, a bigger than, equals to, or less than operator is required. Excel frequently calls for the ability to distinguish between positive and negative values. We will apply the IF function to identify the positive and negative numbers in Excel.
Step 1:
- Here, we will apply the IF function in cell F5.
- And write down the following formula for the E5 cell value whether it is positive or false.
=IF(E5,E5>0,E5<0)
Step 2:
- Here, you will observe the True or False statement for positive and negative numbers respectively.
- Now, you can see the true statement for the E5 cell number, so this is positive.
- Then, use the Fill Handle tool and drag it down from cell F5 to F11.
Step 3:
- Therefore, you will get the result of other cells whether it is positive or negative.
Read More: [Fixed!] Excel Not Adding Negative Numbers Correctly (4 Solutions)
3. Utilizing COUNTIF Function for Counting Positive and Negative Numbers
When you have a lot of data to count, positive and negative numbers in Excel. There is a very convenient method to count positive and negative numbers by utilizing the COUNTIF function in Excel.
Step 1:
- At the start of this section, we will create a sample data set for counting positive and negative numbers.
Step 2:
- Firstly, select cell C15.
- Secondly, select column E.
- Thirdly, write down the following formula.
=COUNTIF(E5:E11,">0")
Step 3:
- Additionally, pressing ENTER will result in a display of the total positive value of column E in cell C15.
- Finally, use the Fill Handle tool and drag it down from cell C15 to C16.
Step 4:
- As a result, cell C16 will convey the total negative number of columns E in cell C15.
Read More: How to Count Negative Numbers in Excel (3 Easy Ways)
4. Applying VBA Code for Identifying Positive and Negative Numbers
In the last section, we will generate a VBA code that makes it very easy to identify positive and negative numbers in Excel.
Step 1:
- At first, we will open the Developer tab.
- Then, we will select the Visual Basic command.
Step 2:
- Here, the Visual Basic window will open.
- After that, from the Insert option, we will choose the new Module to write a VBA code.
Step 3:
- Now, paste the following VBA code into the Module.
- To run the program, click the “Run” button or press F5.
Sub Sign_Function()
'Selecting cell
Myvalue = Range("E6")
If IsNumeric(Myvalue) Then
'Using condition to check positive number
If Sgn(Myvalue) = 1 Then
MsgBox ("Cell E6 is positive")
'Using condition to check blank number
ElseIf Sgn(Myvalue) = 0 Then
MsgBox ("Cell E6 is 0 or blank")
'Using condition to check negative number
ElseIf Sgn(Myvalue) = -1 Then
MsgBox ("Cell E6 is negative")
End If
Else
MsgBox ("Cell E6 is not numeric")
End If
End Sub
VBA Code Breakdown
- Firstly, we give a subject name to our module with Sub Sign_Function().
- Secondly, we will select our cell to identify whether it is a positive or negative number using Myvalue = Range(“E6”).
- Now, we will use condition to identify whether it is a positive or negative number using If Sgn(Myvalue) = 1 Then MsgBox (“Cell E6 is positive”) and ElseIf Sgn(Myvalue) = -1 Then MsgBox (“Cell E6 is negative”).
Step 4:
- Finally, you will see that cell E6 conveys the negative sign.
Conclusion
In this article, I’ve covered 4 handy methods to apply formulas for positive and negative numbers in Excel. I sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
