FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week
Excel In Seconds Tips and Tricks from The Excel Addict - Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

To view this newsletter in your browser click here.


September 20, 2016

Hi fellow Excel Addict,

Thanks for joining me today for another 'Excel in Seconds' tip. I hope you will find today's tip, 'Sum Just The Positive or Negative Numbers In A Range' useful.

If you missed last Thursday's 'Excel in Minutes' tip, 'Creating a Running Total Formula', you can read it here now.

Please feel free to share my newsletter with your friends — chances are they will thank you for it...like my new subscriber Terry M.

Mr. Hayes, In just minutes after finding your website (via a Google search,) my Excel skills doubled. Thank you so much for sharing your discoveries and knowledge with everyone. I cannot convey how appreciative I am that you are so willing to share your experience with the rest of us. - Terry M.

Hope you have a great week and keep on Excelling
,
Francis Hayes (The Excel Addict)
Email: 
fhayes[AT]TheExcelAddict.com


It's about time to learn macros in Microsoft Excel 2007 2010 2013 2016 365



TheExcelAddict.com Quote of the Day

"You will never reach your destination if you stop
and throw stones at every dog that barks."

--Winston Churchill --


To view this newsletter online click here.

If you missed my last newsletter, you can click here to view it online.

You can access even more tips on my website by going to my members' page

To update your contact info, click the 'Change Subscriber Options' at the bottom of this email.



Excel in Seconds with TheExcelAddict.com

Sum Just The Positive or Negative Numbers In A Range

I recently had a request from a subscriber to my newsletter who needed to sum only the positive numbers in a column. At first this may seem like a daunting task but there's actually a very easy solution.

The trick is to use the SUMIF function instead of the SUM function.

The SUMIF Function

=SUMIF(range, criteria, [sum_range])

The first two arguments of the SUMIF function (range, criteria) are required. The square brackets around the third argument (sum_range) tells us that this argument is optional. When the range you want to SUM is the same as the range you are testing against your criteria, you do not need to supply the sum_range argument.

Let's look at a couple of examples...

In column C in the image below, we have both positive and negative amounts. We will need one formula to give us the sum of the positive values and a similar formula to give us the sum of the negative values.

SUM only the positive values
Since the range that we want to sum and the range we want to check for values 'greater than 0' (i.e. criteria) are the same, we don't need to supply the third argument.

To find the total of the positive values we can use the formula...

=SUMIF(C1:C9,">0")

Sum Only Positive Values In Range in Microsoft Excel 2007 2010 2013 2016 365

The SUMIF function sums only the values in the range that meet the criterion (i.e. ">0")

SUM only the negative values
For negative values, we use the same formula except we change the criteria to 'less than 0'. So, to find the total of the negative values we use the formula...

=SUMIF(C1:C9,"<0")

Sum Only Negative Values In Range in Microsoft Excel 2007 2010 2013 2016 365

The SUMIF function sums only the values in the range that meet the criterion (i.e. "<0")



It's about time to learn macros in Microsoft Excel 2007 2010 2013 2016 365

FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week


Thanks for supporting this newsletter and website

Earnings Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that for a few dollars. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are ever not completely satisfied with anything I recommend, please let me know and I will get your money back for you...GUARANTEED. You can't lose.
 'Spreadsheets Tips From An Excel Addict' and 'Excel in Seconds' are publications of TheExcelAddict.com.
Copyright Francis Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630