• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Looking for Solution on Call Center Dash Board

Bhineet

New Member
Hi !
I am looking for the solution for very simple problem but not able to find the reply any where.
I have a data where Column a and B have the details about particular and c onwards for next 7 columns have data of the particular type.
Column a is common so no need to touch . Column B has different type of calls based on skill set.
C onwards we have number of calls ansered / Calls abandoned etc ..
on other sheet we have skill sets groups which are written in column a and b . All skill sets of sheet 1 are there in Column a of sheet2. and these are diveded in four groups in column B.

Now on sheet 3 I want to have the sum of Sheet1 cloumn c to I, based on the four groups using the formula.

I can use the sumif and index or Vlookup and Index , but not able to define that withhout making intermediate sheet , if i an use some formula to have Sheet2 column B , group as reference .
 
Thanks Narayan,

I have updloaded the file. It has been three days I am still looking for solution. On logical front his looks simple but not able to put the pirces together.
 
Thanks Narayan,
Simple but could not figure out the way. However just for the knowledge purpose , is there any other way than using the array formula?
Also If I use dynamic range ? And what if i use Index function to pick the data from base data for the Dash boarding column heading.
 
Two solutions:
1. You can add one more column in base data using Vlookup fromm center name tab. Then using Sumifs or Pivot table you can get final result.
2. If you are using Excel 2013 then create a pivot table from base data and center name. Ensure that you add both to "data model". Then create a relationship between these two data.
Rest you can get details in matrix format similar to pivot table in step 1 above.

Otherwise the array formula solution given aboves works fine.
 
Back
Top