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.
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")
- Then press Enter and you’ll see the expected result.
Read More: Excel Sum If a Cell Contains Criteria (5 Examples)
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.
Follow the step-by-step process given below.
STEPS:
- First, select the range of cells you want to work with.
- Now, select the Table feature under the Insert tab.
- A dialogue box will pop out. Here, check the red-colored box as shown in the image below and press OK.
- After pressing OK, you’ll get your desired Table.
- From here, select the red-colored box.
- 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.
- 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: Sum to End of a Column in Excel (8 Handy Methods)
Similar Readings
- How to Sum Only Visible Cells in Excel (4 Quick Ways)
- Shortcut for Sum in Excel (2 Quick Tricks)
- How to Sum Multiple Rows in Excel (4 Quick Ways)
- Sum If a Cell Contains Text in Excel (6 Suitable Formulas)
- How to Sum Columns in Excel (7 Methods)
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.
STEPS:
- In the beginning, select the Filter feature under the Data tab.
- Now select the red-colored box as shown in the following image.
- 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.
- After that, a final dialogue box will pop out.
- There, type 0 in the red-colored box and press OK.
- 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.
Read More: How to Sum Filtered Cells in Excel (5 Suitable Ways)
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.
- A window will pop out.
- From there, select the Insert tab.
- Then select the Module option.
- 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
- After that, close the Visual Basic window.
- Now, select the range of data cells.
- Afterward, select the Macros feature under the Developer tab.
- 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: How to Add Numbers in Excel (2 Easy Ways)
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
- How to Sum Between Two Numbers Formula in Excel
- Sum Formula Shortcuts in Excel (3 Quick Ways)
- How to Use VLOOKUP with SUM Function in Excel (6 Methods)
- Sum Cells in Excel: Continuous, Random, With Criteria, etc.
- How to Sum Colored Cells in Excel (4 Ways)
- Add Multiple Cells in Excel (6 Methods)
- How to Add Specific Cells in Excel (5 Simple Ways)
