Happy Friday Excellers.  Time for some more Formula Fun.  Today I want to show you how to use SUMIF in Excel to sum only the POSITIVE numbers in your data set.  Let’s take an example.  We have a transactional extract report (below) that shows all sales transactions for Beannie hats which include returns.  So, this good, but we just want to know the values of the sales excluding the returns of Bennie Hats.

All of our sales values are in Column B, under the Sales header.  We can use the SUMIF function to carry out the calculation of summing only our positive values.  Let’s start by hitting the = sign to start our Excel formula in Cell C6.  This is always a good way to see what arguments the Excel function requires.

SUMIF Syntax

SUMIF(range, criteria, [sum_range])

range. This is a requiredargument. The range of cells that you want to be evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

criteria   This is also a required argument. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. In our example, we need to insert the expression “>0”

Important!

Any text criteria or any criteria that include logical or mathematical symbols must be enclosed in double quotation marks (“). If the criteria is numeric, double quotation marks are not required.  We used >in our formula so we must use the quotation marks.

sum_range   This is an opyional argument. The actual cells to add if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).  In this example, we are not using a sum range.

So, the SUMIF formula will look like this- where our range of values is contained in the data range B4 to B15.

Our result is 168.56. This formula has only summed the positive numbers or the sale of Beanies. What about calculating the value of refunds that have been given generated in this extract of transactions?.

SUMMING NEGATIVE VALUES

So, this calculation has given a result of -42.50 of refunds in this data set. In this calculation, we have used the same formula but replaced the >0 with <0. We are simply summing, or adding up the values that are less than 0. The quotation marks are still required as we using mathematical symbols.

How simple was that?. A great way to sum conditionally in Excel

Do You Want To Watch The Video?.

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Formula Friday series. Click on the link below

How To Excel At Excel – Formula Friday Blog Posts.