Today, we are going to count sums and averages with conditions. In our first example, we have a small table where we sell fruit. We want to sum up the whole weight and average weight for a single cell. To sum the values with conditions we can use the SUMIF function. In the first argument, we write the range where we need to check our condition and lock it by pressing the F4 key. In the second argument, which is criteria, we just put a reference to a single cell with the product name. We don’t have to lock the cell because while copying the formula down, we want the fruit names to change. The third argument of the function is the sum range. It is closed in square brackets which means that it is not necessary write it, however we will do it. We want to sum values from the Weight column. Then the F4 key to lock it (Fig. 1).
=SUMIF($B$2:$B$10,F2,$C$2:$C$10)
After all arguments have been written, let’s look at our ranges and try to understand how the SUMIF function works. The function will look at each cell in the Product column and check if they meet the conditions. In the first cell, we have apples, which means that the condition is met. In such a situation, the function will keep on looking for the third argument placed in the same row. If it finds it, it will add the value. If there is another cell that meets the same condition, it will also add the value from the same row (Fig. 2).
Our formula looks as follows (Fig. 3).
=SUMIF($B$2:$B$10,F2,$C$2:$C$10)
After entering and copying down, we can see how many apples, pears and oranges we have sold (Fig. 4).
Now, let’s try to count the average weight of a single sale. We just have to copy our SUMIF formula in the edit mode and paste it into the cell in the Average column. The SUMIF and the AVERAGE functions have the same arguments. We just change the function name. We can see that the name of the third argument changed from sum range into average range, however it is still the same Weight column (fig. 5).
=AVERAGEIF($B$2:$B$10, F2,$C$2:$C$10)
After entering and copying it down, we have our results (Fig. 6).
Let’s move on to our second example. We want to check here what would happen if we give the AVERAGEIF or SUMIF function just two arguments. In this example, we just want to count the average sale. We are starting with a simple AVERAGE function without conditions, so without IFs, for the Sales column (Fig.7).
=AVERAGE(C2:C11)
After entering it, we have the average (Fig. 8).
Now, let’s count the average with a condition. We don’t want to count the value if it equals 0. Let’s write the AVERAGEIF function, select the same range and write the condition. We open double quotes and write <>0 and close it. When we don’t write the third argument in the AVERAGEIF or SUMIF functions, Excel takes the first range as the range in which we are doing our calculations. In this situation Excel doesn’t take empty cells and cells with 0 into calculating the average. After entering it, we can see different results. It’s because our condition was triggered in the AVERAGEIF function, which means that Excel did not count the cells with 0 (Fig. 9).
When we delete the value with 0, we can see that the AVERAGE and AVERAGEIF function return the same results. It’s because functions such as AVERAGE, SUMS, MIN and MAX do not consider empty cells (Fig. 10).
However, when we put 0 into the cells, the AVERAGE function will count it which means that the average will go down (Fig. 11).
Summing up, a simple AVERAGE function counts cells with 0, and the AVERAGEIF function doesn’t due to the condition.
In Excel, in most situations, empty cells are treated as 0.