Table of Contents
Excel is a powerful tool that allows users to perform various calculations and operations on numerical data. One useful function in Excel is the ability to sum values only if they meet certain criteria. In this case, the criteria is that the value must be greater than zero. To do this, users can utilize the SUMIF function, which allows them to specify a range of cells and a criteria, and only sum the values that meet that criteria. By using this function, users can effectively sum only the positive values in a data set, making their calculations more accurate and efficient. This feature is particularly useful when dealing with large amounts of data, as it allows users to quickly and easily ignore any negative values that may skew their results. Overall, using Excel to sum values only if they are greater than zero can greatly improve the accuracy and efficiency of data analysis.
Excel: Sum If Greater Than Zero
You can use the following formula in Excel to only sum values that are greater than zero in a particular range:
=SUMIF(B2:B9, ">0", C2:C9)
This particular formula calculates the sum of values in the range C2:C9 only where the corresponding cell in the range B2:B9 is greater than zero.
The following example shows how to use this formula in practice.
Example: SUMIF Greater Than Zero in Excel
Suppose we have the following dataset in Excel that contains information about ratings for various hotels along with the number of customers who stayed at each hotel during the past week:
Suppose we would like to calculate the sum of customers only for the hotels that have a rating greater than zero.
We can type the following formula into cell E2 to do so:
=SUMIF(B2:B9, ">0", C2:C9)
The following screenshot shows how to use this formula in practice:
We can see that the sum of customers only for hotels with a rating greater than zero is 167.
We can confirm this by manually identifying each of these hotels:
We can confirm that the sum of customers for these hotels is 167:
Sum of Customers: 15 + 22 + 28 +54 + 12 + 36 = 167
=SUMIF(B2:B9, ">=0", C2:C9)
Additional Resources
The following tutorials explain how to perform other common operations in Excel: