I have descriptions in column A and both negative and positive values in Column B. I wish to add only the positive values but am unable to do it although I’m a regular user of Excel. I have tried to sort and also hide the data to achieve my goal but without success. Thanks for your help in advance.
An interesting question! I also started thinking about how to do this when I first saw your data and then realized quickly that the addition of the positive numbers could be done using the SUMIF function with the proper criteria. In fact, you can also add the negative numbers using the same idea but another criteria.
I really feel that the simplicity of the question makes it powerful and interesting, isn’t it?
The SUMIF function combines the power of SUM and IF functions and can help us add positive or negative values or both depending on the criteria we apply. For example if we positive and negative numerical values in our data in column B, we can add only the positive values using the following formula:
=sumif (B2:B7,”>0″) Here we assume that the numerical values are in cells B2 to B7 and our function is applied in cell B8.
In a similar fashion we can add the negative values by using the SUMIF function in the following manner: =sumif (B2:B7, “<0”). Again we assume that our data is in the cells B2 to B7 and we use the function in cell B8 or a cell of our choice.
Another elegant method to add positive or negative numbers in column B would be to use a looping process in a macro. But normally if you can do the job without using Excel VBA or macro, most users prefer using the SUMIF function in such a case.
Watch the training video below to see how the SUMIF function is used to add only positive values from a range of negative and positive numerical values.