Tuesday, 20 May 2014

How to Find the Sum based on Multiple Criteria using SUMPRODUCT Function

Excel SUMPRODUCT 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-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



No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts