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) then 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 to get Actuals and Forecast Numbers for Quarters Calculated based the Conditions.
By using the below formulas , Actual Numbers will shown 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))

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

2 comments:

  1. Thanks ffor sharing your thoughts. I truly appreciate your efforts annd I will be waiting
    ffor your further write ups thhank you once again.

    ReplyDelete
  2. You are so awesome! I don't believe I have read a single thing
    like that before. So good to discover somebody with a few unique
    thoughts on this subject matter. Seriously.. thank you for starting
    this up. This site is something that is needed on the internet,
    someone with some originality!

    ReplyDelete

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts