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:
As you cans see that some values of gross profit are positive and some are negative and some even zero. We want to:
- Sum values only if they are positive
- Average values only if they are positive
- 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″)
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″)
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″)
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.