Saturday, 7 February 2015

SUMIFS Function with Index Match Multiple Criteria to Find the Sum

SUMIFS Function to Find the Sum with Index Match Multiple Criteria
Suppose we have a data of different Regions with multiple Entities showing Monthly breakup as shown below ;

Eg :

The below data show the data for different Region-Levels(Eg: Tamatam-East,Tamatam-West..) ,different Region-Entities(ABC,BCD,DEF...),with Monthly breakup(201501,201502..).

This data contains the Actual (the data of closed months) and Forecast(the data of future months).

Source Data :

From the above data we want to Sum up the data by Quarterly in Result sheet as shown below , based on the following conditions :
Q1=Sum(Actual Months)............
Q2=Sum(Actual Months)............
Q3=Sum(Forecast Months)........
Q4=Sum(Forecast Months)............

Desired Output :

How to Do this :
In the Source Data Tab we have to Create a Common concatenated Column(Region-Levels&Region-Entity) to use in the Index Match criteria for Result Data Tab as below :

Next In the Result Tab also we have to Create a Common concatenated Column(Region-Levels&Region-Entity) the we have to Create a Index Column where we will get the Row Index Number of  the Matching value from  Source_Data Tab as shown below using the following formula :


Next Apply the SUMIFS Formula as shown below get Actuals and Forecast Numbers for Quarters Calculated based the Conditions. 

By using the below formulas , Actual Numbers will shown under under the Quarters with flag (Act) and Forecast Numbers will shown under the Quarters with flag (Fcst) as shown in the Source data.

For Actuals :

For Forecast/Plan:

For detailed understanding of the above process , you can download the sample file from below :

          [ BI Reporting Analyst ]

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts