IndexMatch

[rkr]

Hi,

PFA

Please suggest me how to achieve this.

 

[Sathish KV]

Hi,

Do you want to split the data in to different sheet with Region wise

 

[Hui]

Wouldn't a Pivot table do?
Select the data area
Insert, Pivot table
Complete as below:

 

[rkr]

Hi,

Do you want to split the data in to different sheet with Region wise

Hi Satish,

I want to split the data, but the data has to be in single sheet.

Thanks,

 

[Sathish KV]

Hi,

AS Hui said, it can be achieved through pivot table

Else if you would require a macro I can do that!

 

[rkr]

Wouldn't a Pivot table do?
Select the data area
Insert, Pivot table
Complete as below:
View attachment 8948

Hi Hui,

Thanks for the solution.

But i dont want to use Pivot Table. I have to use the calculated data in Dashboard tool. Dashboard tool cannot accept Pivot Table.

I achieved this using VLOOKUP. But the probelm is, If we use VLOOKUP more than 1000 times, we cant load the excel into my tool.

So, i am trying to achieve this using INDEX/MATCH functions.

please see the below function that i have written. FYI, for instance i hardcoded the States in a Region. I am not getting the States using excel functions. But to get the Sales values i have written the below formula.

=IF(ISNA(INDEX($H$5:$H$9430,MATCH(1,($L$6 = $C$5:$C$9430)*($M$5=$E$5:$E$9430),0))),"",INDEX($H$5:$H$9430,MATCH(1,($L$6 = $C$5:$C$9430)*($M$5=$E$5:$E$9430),0)))


Here the problem is , i am getting sales data of the first match of the array. But i want to sum all the Sales of a CustomerSegment in a State in a Region.

How can i modify the above function to include sum of all the matches.

Please tell me if i am not explaining properly to make u understand.

Thanks,
Ram

 

[Hui]

I'd use Sumproduct not Index/Match
First select the data table and goto,
Formulas, Create from selection,
Choose Top only
W6: =SUMPRODUCT((Region=$V$5)*(State_or_Province=$V6)*(Customer_Segment=W$5),Sales)
You need real values in the Region and State cells around the Table

 

[rkr]

I'd use Sumproduct not Index/Match

I am not good in excel Hui.

let me browse it, how to use Sumproduct .

Thanks

 

[Hui]

See my previous post it is updated

 

[Hui]

Apply a Conditional Format to hide the repeat Headings in Region :

 

[rkr]

Apply a Conditional Format to hide the repeat Headings in Region :

View attachment 8952

Hi Hui,

Thanks for the suggestion to apply conditional rules and solution.

I wrote the formula like this.

=SUMPRODUCT(--($B$5:$B$9430=$L$4), --($C$5:$C$9430=$L15),--($E$5:$E$9430 = $M$5),$H$5:$H$9430)

It is working fine in Excel.. Checking in my tool.