Sum, Average or Count only Positive numbers in Excel – SUMIF + AVERAGEIF + COUNTIF explained

0
976

The IFs and BUTs are probably one the most annoying things in real life but they are super cool in Excel when it comes to formulas. Today we are discussing three of IF condition formulas that help us do SUMs, COUNTs and AVERAGEs easily.

Note: To better understand their application I will be taking “positive” numbers as a condition.

Suppose you have profit calculation of whole year for each month. Here is the data:

sumif average if countif

As you cans see that some values of gross profit are positive and some are negative and some even zero. We want to:

  1. Sum values only if they are positive
  2. Average values only if they are positive
  3. Count values only if they are positive

SUMIF – Sum only positive numbers in the range

To sum only positive numbers use the following formula:

=SUMIF(D5:D16,”>0″)

sumif only positve numbers

AVERAGEIF – Average only positive numbers in the range

To average only positive numbers (excluding zero as zero is neither positive nor negative number) use the following formula:

=AVERAGEIF(D5:D16,”>0″)

averageif positive value

COUNTIF – Count only positive numbers in the range

On similar basis we can count only positive numbers in the range as well. Use the following formula to do the job:

=COUNTIF(D5:D16,”>0″)

countif positive value

Understanding and taking it further

In all the above formulas if you observe the syntax is same. First you mention the range from which you want to sum, average or count the numbers  and then you mention the condition. For these formulas however the point to note is that condition is expressed in double quotes.

Understanding this if one has to sum negative numbers only then you have have to change the condition from “>0” to “<0” and that’s it. Similarly if one wants to count number of values that are zero then condition will be “=0”. Give these options a try.

Check out following tutorials to excel even more at Excel:

  1. 10+ ways to SUM Top N values in data range – Ready to be Ben 10 of Excel?
  2. Using SUMIF with Dates in Excel
  3. Create Data Validation lists from Unsorted data with duplicates in Excel [How To]

LEAVE A REPLY