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 :
Region-Levels="Tamatam-East","Tamatam-West"...
Region-Entity="ABC","BCD"....
Status="Act","Fcst".....
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 :

=IFERROR(ROW(INDEX(Source_Data!$A$5:$A$20,MATCH(A4,Source_Data!$A$5:$A$20,0),)),"")


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 :
=SUMIFS(INDIRECT("'Source_Data'!$D"&$B4&":$O"&$B4),Source_Data!$D$3:$O$3,F$2,Source_Data!$D$4:$O$4,$E4)

For Forecast/Plan:
=IF(COUNTIFS(Source_Data!$3:$3,Result!F$2,Source_Data!$4:$4,"Act")=3,"-",SUMIFS(INDIRECT("'Source_Data'!$D"&$B11&":$O"&$B11),Source_Data!$D$3:$O$3,F$2,Source_Data!$D$4:$O$4,$E11))


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


Thanks,
TAMATAM
          [ BI Reporting Analyst ]

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts