Excel has some of the most powerful functions for us in order to make our work much easier.
Functions under discussion are considered as advanced excel functions.
In this tutorial, we will learn the difference between the following functions as follow:
SUMIF function help us to add different values based on one condition/criteria in an array.
The formula of the SUMIF is as follow:
=SUMIF(range, criteria, [sum range])
Our data set is as follow:
Figure 1: Data Set
Based on our data set we want to know how much kg of vegetable & fruits “June” bought over the time.
In cell “H2”, write formula SUMIF, in “range” portion select Column “C” (Name), criteria will be “June” & in “Sum Range” portion of formula select column “E” (kg).
Figure 2: SumIf Formula
Below mentioned screenshot will show the formula & cell references:
Figure 3: Selecting Formula Range
After entering the formula, the result is as follow:
Figure 4: Completing Formula & Result
SUMIFS function help us to add different values based on multiple condition/criteria in an array.
The formula of the SUMIFS is as follow:
=SUMIF(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]……. )
In SUMIFS, criteria2 and onward are optional. Now we continue with the same data set.
Now we want to know how much kg “June” bought in Food Type “Fruit”.
Figure 5: SumIFs Formula
Here our sum range will be column “E” i.e, Kg, criteria 1 will be “June” and criteria 2 will be Food Type “Fruit”. The screenshot is as follow:
Figure 6: SumIFs Formula Selecting Range
And the final result is as follow:
Figure 7: SumIFs Formula Result
The SUMPRODUCT function multiples ranges or arrays together and return the sum of products. SUMPRODUCT is a very versatile function.
=SUMPRODUCT(array1, [array2], ….)
For the sake of SUMPRODUCT, we consider price of vegetable & fruit.
Figure 8: SUMPRODUCT data
Now we want to calculate total sale of both vegetables & fruit.
Figure 9: SUMPRODUCT Formula Selecting Range
Here SUMPRODUCT function is first multiplying values of array1 with array2 and then summing the product value of array1 & array2.
Figure 10: SUMPRODUCT Formula Result
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.