How to Sum Only Positive Numbers in Excel (4 Simple Ways)

We can easily Sum a range of Excel datasheets. But, quite frequently we need to add the cell values following a condition. This article will show you how to Sum Only Positive Numbers in Excel very easily and effectively with some examples.

To help you understand better, I’m going to use a sample dataset as an example. The following dataset represents the Profit/Loss in the first 5 Days of January of a store.

excel sum only positive numbers


Download Practice Workbook

Download the following workbook to practice by yourself.


4 Simple and Effective Ways in Excel to Sum Positive Numbers Only

1. Sum Only Positive Numbers by Applying SUMIF Function in Excel

The SUMIF function in Excel is very useful when adding up cell values following criteria. Here, we’ll use the SUMIF function to add only the values that are greater than 0.

Let me show you the step-by-step process.

STEPS:

  • At first, select cell C10.
  • Here, type the formula:
=SUMIF(C5:C9,">0")

Sum Only Positive Numbers by applying SUMIF Function in Excel

  • Then press Enter and you’ll see the expected result.

Sum Only Positive Numbers by applying SUMIF Function in Excel

Read more: How to Sum Between Two Numbers Formula in Excel


2. Use of Excel Table Feature to Sum Positive Numbers Only

The Excel Table feature is really helpful while doing operations following certain conditions. In this method, we are going to use the Table feature in Excel to Sum Positive Numbers Only.

Use of Excel Table feature to Sum Positive Numbers Only

Follow the step-by-step process given below.

STEPS:

  • First, select the range of cells you want to work with.

Use of Excel Table feature to Sum Positive Numbers Only

  • Now, select the Table feature under the Insert tab.

Use of Excel Table feature to Sum Positive Numbers Only

  • A dialogue box will pop out. Here, check the red-colored box as shown in the image below and press OK.

Use of Excel Table feature to Sum Positive Numbers Only

  • After pressing OK, you’ll get your desired Table.

Use of Excel Table feature to Sum Positive Numbers Only

  • From here, select the red-colored box.

Use of Excel Table feature to Sum Positive Numbers Only

  • A dialogue box will pop out as shown below in red-colored box 1.
  • Then select the Number Filters.
  • Another dialogue box will pop out like the red-colored box 2.
  • From here select Greater Than.

Use of Excel Table feature to Sum Positive Numbers Only

  • A new dialogue box will pop out.
  • Type 0 in the red-colored box and press OK.

  • After pressing OK, you’ll see your Table with only the Positive Numbers.

  • After that, click any cell inside the Table.
  • Now check the Total Row feature under the Table Design tab.

  • Finally, you’ll get the Sum in the cell right under the Table.

Read more: How to Add Numbers in Excel


Similar Readings


3. Application of Filter to Sum Positive Numbers in Excel

Excel Filter feature is a powerful tool and can be applied in various operations. In this method, we’ll Sum the Positive Numbers Only using the Filter function in Excel.

Application of Filter to Sum Positive Numbers in Excel

STEPS:

  • In the beginning, select the Filter feature under the Data tab.

Application of Filter to Sum Positive Numbers in Excel

  • Now select the red-colored box as shown in the following image.

Application of Filter to Sum Positive Numbers in Excel

  • Here, you’ll get a dialogue box.
  • Then select the Number Filters option shown as marked 1 in the following image.
  • Another dialogue box will pop out.
  • There, select the Greater Than option shown as marked 2 in the image.

Application of Filter to Sum Positive Numbers in Excel

  • After that, a final dialogue box will pop out.
  • There, type 0 in the red-colored box and press OK.

Application of Filter to Sum Positive Numbers in Excel

  • After pressing OK, you’ll see that only Positive Numbers are left.

  • Afterward, select cell C11.
  • Then, press down ‘Alt’ and ‘=’ together.

  • Finally, press Enter and the accurate Sum will appear in cell C11.

Application of Filter to Sum Positive Numbers in Excel


4. Sum Only Positive Numbers with Excel VBA

The last method we have here is a bit different than the above-described ones. But if you’re a fan of coding, then this Excel VBA Method is for you. You can very easily generate a macro to Sum Positive Numbers in a range with the help of VBA.

You can find the Visual Basic feature under the Developer tab as shown in the following picture. The precise guideline is given below.

STEPS:

  • Firstly select the Visual Basic feature.

Excel Sum Only Positive Numbers by VBA Method

  • A window will pop out.
  • From there, select the Insert tab.
  • Then select the Module option.

Excel Sum Only Positive Numbers by VBA Method

  • Another window will pop out.
  • There, copy the Code given below and paste it into the Module window.
Sub add_positive_numbers_only()
Dim sheet As Worksheet
Dim rg As Range
Dim result As Range
Set sheet = Application.ActiveSheet
Set rg = Application.Selection
Set result = Application.InputBox( _
Title:="Select the Cell for Showing Result", _
Prompt:="Select the Cell where you want to see the Sum", _
Type:=8)
result.Value = Application.WorksheetFunction.SumIf(rg, ">0")
End Sub

Excel Sum Only Positive Numbers by VBA Method

  • After that, close the Visual Basic window.
  • Now, select the range of data cells.

Excel Sum Only Positive Numbers by VBA Method

  • Afterward, select the Macros feature under the Developer tab.

Excel Sum Only Positive Numbers by VBA Method

  • Then select the Macro name ‘add_positive_numbers_only’ and press Run.

  • A new dialogue box will pop out.
  • There, select the cell where you want to see the Sum result.
  • In this example, select cell C10 and press OK.

  • Eventually, you’ll see the correct Sum result in the cell you selected.

Read more: Excel Sum Last 5 Values in Row (Formula + VBA Code)


Conclusion

So that’s how you can Sum only Positive Numbers in Excel using any of the methods described above. Tell us in the comment section below if you have got any more ways. I’ll also appreciate your kind suggestions or queries.


Further Readings

Aung

Aung

I'm Aung. Recently I've earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo