How to Apply Formula for Positive and Negative Numbers in Excel

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.

Sample Data


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.

Sample Data

Step 2:

  • Firstly, select cell D13.
  • Secondly, select column F.
  • Thirdly, write down the following formula.
=SUMIF(F5:F11,">0")

Sample Data

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.

Sample Data

Step 4:

  • Therefore, we will get the sum of all the negative numbers in cell D14.

Sample Data

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)

Sample DataStep 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.

Easy Ways to Apply Formula for Positive and Negative Numbers in Excel

Step 3:

  • Therefore, you will get the result of other cells whether it is positive or negative.

Easy Ways to Apply Formula for Positive and Negative Numbers in Excel

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.

Easy Ways to Apply Formula for Positive and Negative Numbers in Excel

Step 2:

  • Firstly, select cell C15.
  • Secondly, select column E.
  • Thirdly, write down the following formula.
=COUNTIF(E5:E11,">0")

Easy Ways to Apply Formula for Positive and Negative Numbers in Excel

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.

Easy Ways to Apply Formula for Positive and Negative Numbers in Excel

Step 4:

  • As a result, cell C16 will convey the total negative number of columns E in cell C15.

Easy Ways to Apply Formula for Positive and Negative Numbers in Excel

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.

Easy Ways to Apply Formula for Positive and Negative Numbers in Excel

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.

Easy Ways to Apply Formula for Positive and Negative Numbers in Excel

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

Easy Ways to Apply Formula for Positive and Negative Numbers in Excel

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.

Sample Data


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.


Related Articles

Bishawajit Chakraborty

Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo