Excel tutorials

Sometimes we have some values that we want to calculate. We only want to calculate them if the corresponding values are not blanks.

In this part we are going to use the SUMIFS function. Lets clarify with an example.

Above we have a list of fruits and its price and color in some cases. Our goal is to calculate the total price. We only want to do that when there is a color in column E.

The total price without considering the colors are :

=SUM(Table[Price per kg])

This will return 22.

Now we can use the SUMIFS to take blanks into consideration.

In Cell G7 – enter the below formula:

=SUMIFS(D5:D11;E5:E11;”<>”)

The first argument is the range that we want to sum. The other is the criteria. The values in the range E5:E11 should not be empty.

The result as you can see will be 16.