SUM and AVERAGE with condition — SUMIF and AVERAGEIF functions

Today, we are going to count sums and aver­ages with con­di­tions. In our first exam­ple, we have a small table where we sell fruit. We want to sum up the whole weight and aver­age weight for a sin­gle cell. To sum the val­ues with con­di­tions we can use the SUMIF func­tion. In the first argu­ment, we write the range where we need to check our con­di­tion and lock it by press­ing the F4 key. In the sec­ond argu­ment, which is cri­te­ria, we just put a ref­er­ence to a sin­gle cell with the prod­uct name. We don’t have to lock the cell because while copy­ing the for­mu­la down, we want the fruit names to change. The third argu­ment of the func­tion is the sum range. It is closed in square brack­ets which means that it is not nec­es­sary write it, how­ev­er we will do it. We want to sum val­ues from the Weight col­umn. Then the F4 key to lock it (Fig. 1).

=SUMIF($B$2:$B$10,F2,$C$2:$C$10)

After all argu­ments have been writ­ten, let’s look at our ranges and try to under­stand how the SUMIF func­tion works. The func­tion will look at each cell in the Prod­uct col­umn and check if they meet the con­di­tions. In the first cell, we have apples, which means that the con­di­tion is met. In such a sit­u­a­tion, the func­tion will keep on look­ing for the third argu­ment placed in the same row. If it finds it, it will add the val­ue. If there is anoth­er cell that meets the same con­di­tion, it will also add the val­ue from the same row (Fig. 2).

Our for­mu­la looks as fol­lows (Fig. 3).

=SUMIF($B$2:$B$10,F2,$C$2:$C$10)

After enter­ing and copy­ing down, we can see how many apples, pears and oranges we have sold (Fig. 4).

Now, let’s try to count the aver­age weight of a sin­gle sale. We just have to copy our SUMIF for­mu­la in the edit mode and paste it into the cell in the Aver­age col­umn. The SUMIF and the AVERAGE func­tions have the same argu­ments. We just change the func­tion name. We can see that the name of the third argu­ment changed from sum range into aver­age range, how­ev­er it is still the same Weight col­umn (fig. 5).

=AVERAGEIF($B$2:$B$10, F2,$C$2:$C$10)

After enter­ing and copy­ing it down, we have our results (Fig. 6).

Let’s move on to our sec­ond exam­ple. We want to check here what would hap­pen if we give the AVERAGEIF or SUMIF func­tion just two argu­ments. In this exam­ple, we just want to count the aver­age sale. We are start­ing with a sim­ple AVERAGE func­tion with­out con­di­tions, so with­out IFs, for the Sales col­umn (Fig.7).

=AVERAGE(C2:C11)

After enter­ing it, we have the aver­age (Fig. 8). 

Now, let’s count the aver­age with a con­di­tion. We don’t want to count the val­ue if it equals 0. Let’s write the AVERAGEIF func­tion, select the same range and write the con­di­tion. We open dou­ble quotes and write <>0 and close it. When we don’t write the third argu­ment in the AVERAGEIF or SUMIF func­tions, Excel takes the first range as the range in which we are doing our cal­cu­la­tions. In this sit­u­a­tion Excel does­n’t take emp­ty cells and cells with 0 into cal­cu­lat­ing the aver­age. After enter­ing it, we can see dif­fer­ent results. It’s because our con­di­tion was trig­gered in the AVERAGEIF func­tion, which means that Excel did not count the cells with 0 (Fig. 9). 

When we delete the val­ue with 0, we can see that the AVERAGE and AVERAGEIF func­tion return the same results. It’s because func­tions such as AVERAGE, SUMS, MIN and MAX do not con­sid­er emp­ty cells (Fig. 10).

How­ev­er, when we put 0 into the cells, the AVERAGE func­tion will count it which means that the aver­age will go down (Fig. 11).

Sum­ming up, a sim­ple AVERAGE func­tion counts cells with 0, and the AVERAGEIF func­tion does­n’t due to the condition. 

In Excel, in most sit­u­a­tions, emp­ty cells are treat­ed as 0. 

https://www.youtube.com/watch?v=5h7hVwUjoWA