Excel SUMIF Function to Sum Values Greater Than 0

Usually, the SUMIF function in excel works with specific values or conditions. And one of the most common conditions we use is summing the values greater than 0. So, this is very helpful and highly used when working with datasets including total inventory or even profits or losses of a company.

In other cases, sometimes, we also work with data including both negative and positive values. So, in such a scenario, we may need to sum values greater than 0 in excel. To do so, the SUMIF function would be the best one to try.

Overview of the Excel SUMIF Function

By default, the SUMIF function in excel sums the values in a range that meets a specific condition. Normally, that criterion could be a number, text, or date, but not a cell color.

Syntax:

=SUMIF(range, criteria, [sum_range])

➤ range → The cells you want Excel to check against the condition.
➤ criteria → The condition that determines which cells to include.
➤ sum_range → The actual cells to sum. This is optional because if omitted, Excel sums the cells in range.

Example:

While using the SUMIF function to sum only the values greater than 0, this would be the simplest and most efficient method you can try. Unlike summing by colors or custom tricks, the generic SUMIF formula works directly with logical conditions. So it’s clean, fast, and widely used.

As we mentioned earlier, the SUMIF checks each value in a given range and adds it to the total only if it meets your condition. In this case, our condition is simply >0 which means we want to include only positive values.

For example, here we have a dataset including some Product Names, their Total Sales, and Profit/Loss. In the following image, you can see that the Profit/Loss column contains both positive and negative values. The positive numbers mention the profits while the negative numbers refer to the losses.

Now, let’s say we’ll calculate the Total Profit by summing only the positive values from Column C (Profit/Loss) and here’s how it goes.

Steps:

➤ Firstly, select the cell C13 where you want to calculate the sum.
➤ Next, insert the following formula in the cell:

➤ Now, press Enter and you’ll get the sum of only the positive values in column C.

As we can see in the image above, here’s only calculating the positive numbers and ignoring 0 and the negative numbers. So, with this generic SUMIF formula, you can quickly sum up only the profits while ignoring losses and zero values in any dataset.

Besides the SUMIF generic formula, we can also apply SUMIF in some other situations. Like, sometimes, we may need to sum values from one column but only if another related column contains positive numbers. For example, in our dataset, we want to sum the Total Sales from Column B only for those products where the Profit/Loss in Column C is greater than 0.

This can be easily done using the SUMIF function by setting the condition on Column C while summing the values from Column B. To do so, below are the steps to follow.

Steps:

➤ In an empty cell like B13, enter the formula as follows:

➤  Tap Enter and you’ll get the sum of Total Sales values where the Profit is greater than 0.

Here the SUMIF function only sums the Total Sales when there are only positive values in Column C. so, in the formula, C2:C11 means the range Excel checks for the condition (Profit/Loss) and B2:B11 is for the range of values to sum (Total Sales).

Instead of writing the condition like >0 directly in the formula, we can also use a cell reference to specify the zero. This method is more flexible as we can simply change the condition without editing the formula.

So, now let’s assume we’ll sum the Total Sales in Column B only when the Profit/Loss ($) in Column C is greater than a value typed in another cell. Here is how it goes.

Steps:

➤ In cell B13, insert the condition $0.
➤ Now in the cell B14, type the following formula:

➤ Hit the Enter key. And now we get the same result as the previous formula instead of using the cell reference rather than the condition 0 in the formula.

So, if we change the cell reference, the result will ultimately change and we don’t even disrupt the formula. For example, let’s convert the reference to 50 and see what happens.

Once we’ve included the condition greater than 50 in cell E2, it ultimately skipped the amount 40 from Column C. As a result, the sales amount 100 from column B has not been counted and the sum has lessened to 3450 from 3550.

Frequently Asked Questions

Will SUMIF include zeros or negative numbers?

No, it won’t. When we use >0, the SUMIF function ignores zero and negative numbers, only summing positive values.

What if there are blank cells in the range?

No worries. SUMIF ignores blank cells. Only numeric values that meet the condition will be summed.

Can I combine multiple conditions, like greater than 0 and less than 40?

Yes, you can. But you just need to use the SUMIFS function instead of the SUMIF and insert the following formula:

=SUMIFS(B2:B10, B2:B10, “>0”, B2:B10, “<500”)

Concluding Words

So, this was simple. Using the SUMIF function, you can easily calculate totals for positive sales, profits, hours worked, or any dataset where only positive values matter. And with a lot of work values like these, SUMIF becomes an essential function for efficient data analysis. Mastering it can save time, reduce errors, and make your Excel worksheets much more insightful.