Using SUMPRODUCT in MS EXCEL alternatively….
In my
previous post I took you through the basic use of SUMPRODUCT. In this post, I
will explain how to use SUMPRODUCT effectively and alternatively in MS EXCEL.
SUMPRODUCT
can also be used to find the results for specific data in MS EXCEL.
For
example, if you wish to calculate the results/sales of a particular year in a
particular region, SUMPRODUCT becomes very handy for doing so.
Let
us understand this with the help of an example.
 |
MS EXCEL - SUMPRODUCT |
As a manager, I wish to know the sales in year 2000 and 2001
for both the products a and b.
In
such a situation, SUMPRODUCT in MS EXCEL brings out excellent results by just
using a single formula.
SUMPRODUCT
function has been designed to select values in accordance with the desired
criteria.
In
the example shown above, the expression =SUMPRODUCT (($A$2:$A$7=2000) helps SUMPRODUCT to filter values relating to the year 2000.
Going
to the next part in the formula, i.e. ($B$2:$B$7="a").
This
expression enables the filtering of the region (a) in accordance with the
desired year (which is 2000 in the example).
Once
this criteria for calculation is entered in the SUMPRODUCT function, you can
enter the usual parameters of the function i.e. array1, array2, etc. In the
example, the respective arrays are $C$2:$C$7 and $D$2:$D$7
The
results for other year(s) and region(s) may be calculated by changing the
criteria part in the formula. In the context of the example, one can change the
year to 2001(in place of 2000) and the region to b (in place of a).
Note
that $ sign has been added with parameters in order to enable absolute
references.
Happy
learning!!
Will
see you soon with my next post on MS EXCEL
Feel
free to give your comments on the post.