Excel Sum Only Positive Numbers

Happy Friday Excellers. Time for some more Formula Fun. Today I want to prove you how to use SUMIF in Excel to sum only the POSITIVE numbers in your information prepare. Let'southward 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. Nosotros can use the SUMIF role to carry out the adding of summing only our positive values. Let's offset by hitting the = sign to first 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. Bare and text values are ignored.

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

Important!

Any text criteria or whatsoever criteria that include logical or mathematical symbols must exist enclosed in double quotation marks ("). If the criteria is numeric, double quotation marks are not required. Nosotros used >in our formula so nosotros must apply the quotation marks.

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

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

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

SUMMING NEGATIVE VALUES

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

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