Sunday, 30 September 2012

How to Use SumProduct Function with Range Names

Excel Sum Product Function
SUMPRODUCT Returns the sum of the products of corresponding ranges or arrays. The SUMPRODUCT is a multipurpose function used to find the count and sum based on multiple criteria.
Sumproduct is a Most Powerful Function that can did the Job of the functions like Sum, SumIf, Sumifs, Count, Countif and Countifs.

Syntax:
=SUMPRODUCT (Array1, Array2, Array3, ...)
Example:

Let us suppose if we define the List Names as follows :

SalExeID = A3:A26
Product   = B3:B26
Jan          = C3:C26
Feb          = D3:D26
Mar          = E3:E26
Sales       = C3:E26
Then by using the above List Names in Sumproduct Function , We Can Find The Sum and Count As Follows.
Range/List Names Used in Sumproduct To Find Sum:
Ex1:

Ex2:

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------