Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


How to sum / average ignore negative values in Excel? 

Normally, we can apply the SUM and AVERAGE functions to calculate the result of a range of cells including positives and negatives, but, how can we sum or average numbers ignore the negative values in Excel? Here, I will talk about some quick tricks to deal with this problem.

Sum / Average ignore negative values with formulas

Sum / Average ignore negative values with Kutools for Excel

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial

Sum / Average ignore negative values with formulas

To sum or average all values excluding the negative numbers, please apply the following formulas:

Sum ignore negative values:

1. Enter this formula into a blank cell where you want to put the result, =SUMIF(A1:D9,">=0"), see screenshot:

doc ignore negative 1

2. Then press Enter key to get the result, see screenshot:

doc ignore negative 2

Average ignore negative values:

To average ignore the negative values, please use this formula: =SUMIF(A1:D9,">=0")/COUNTIF(A1:D9,">=0").

Note: In the above formulas, A1:D9 is the data range that you want to use, you can change the cell reference as your need.

Sum / Average ignore negative values with Kutools for Excel

Kutools for Excel’s Select Specific Cells feature can help you to select all non-negative numbers at once, and get the calculations, such as sum, average, count at the status bar.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. 

After installing Kutools for Excel, please do as follows:

1. Select the cells that you want to use.

2. Click Kutools > Select > Select Specific Cells, see screenshot:

doc ignore negative 3

3. In the Select Specific Cells dialog box, do the following operations:

(1.) Select Cell from the Selection type;

(2.) Choose Greater than or equal to from the Specific type, and enter 0 in the text box.

doc ignore negative 4

4. Then click Ok or Apply button, all numbers exclude negatives have been selected, and you can view the result of the average, count, sum calculations at the bottom of the status bar, see screenshot:

doc ignore negative 5

Click to know more about this Select Specific Cells utility…

Download and free trial Kutools for Excel Now !

Demo: Sum / Average ignore negative values with Kutools for Excel

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!

Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for you.

  • Designed for 1500+ work scenarios, helps you solve 80% Excel problems.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Being used by 110,000 elites and 300+ well-known companies.

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.