Tuesday, 20 May 2014

SUMPRODUCT Function To Find the Sum based on Multiple Criteria

SUMPRODUCT Function To Find the Sum based on Multiple Criteria
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-I : Working with Multiple Criteria:
Let us consider the following example where we have the data by Months and Quarters.
Here if you want to find Total sum for 'FY' ,which means sum of All months data(excluding Quarterly Sums) , which can be done by using the following Sumproduct formula with multiple criteria :
=SUMPRODUCT((($A$1:$P$1<>"Q1")*($A$1:$P$1<>"Q2")*($A$1:$P$1<>"Q3")*($A$1:$P$1<>"Q4"))*(A2:P2))

---------------------------------------------------------------------------------------------------------------------------------
Example-II:

Formula Usage to find Sum:


Note :
We can use this logic in different scenarios based on our requirement.

Thanks,
TAMATAM
     [ BI-Reporting Analyst ]



No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts