Help with INDEX and MATCH but must look within a certain range which is different daily

Hi Experts,

I have a one which is beyond me. I have included my spreadsheet to help illustrate what I am about to explain because it may not make sense up front.

I have two sheets, one containing the raw data and another where I want to do the calculation

• April RAW DATA = this sheet where the raw data is
• TEAM 1 SUMMARY = this is where the calculation needs to happen for Team 1

The RAW DATA sheet contain the statistics for each team, however, the data is broken down as follows:

• HOUR OF DAY (horizontally) - Lets call this the header row.
• VALUE OF ABANDONMENT FOR EACH TIME BY HOUR (horizontally)
• LIST OF TEAM (Vertically)

The scenario is we have several teams who answer calls in a call center and I am trying to get the number of calls abandonned by hour

EXAMPLE:
Hour > 1 3 7 9 12 14 22
Team1 1 1 2 1 1 1 1
Team2 1 2 3 1 1 6 1
Team3 1 4 8 1 1 5 2

What I am trying to do is return the exact value under each hour for each team. The problem is the hours are not fixed, the raw data will only show the hour for which a value appears which I think a dynamic formula will be needed to do the calculation.

If you view the attached spreadsheet, it will give you a better understanding of what I need to do.

Download here: April.xlsx

Thank you for your support!

Originally Posted by :) Sixthsense :)

In B1 Cell of TEAM_1 Sheet

=SUBSTITUTE(TRIM(SUBSTITUTE(UPPER(A1),"SUMMARY",""))," ","_")

In B3 Cell of TEAM_1 Sheet

=IFERROR(SUM(INDEX('April RAW DATA'!$B$4:$UG$12,MATCH($B$1,'April RAW DATA'!$A$4:$A$12,0),MAX(0,SUMPRODUCT(--MAX(('April RAW DATA'!$B$1:$UG$1=[@[HOUR -->]])*('April RAW DATA'!$B$3:$UG$3=VALUE(Table1[[#Headers],[0]]))*COLUMN('April RAW DATA'!$B$1:$UG$1)))-COLUMN('April RAW DATA'!$B$1)+1))),"-")

Drag it down and right...

Thank you for your assistance. I am not sure its working as I need it to though..

Following your instructions, in B3 it gave me the value of 4 -- this figure seems to be the SUM of the entire row for Team_1.

What I need is for each day, the value shown for each hour for Team_1

I am pretty sure I followed your instructions correctly.. should I end up with the number 4 in B3 ??

Thanks for offering to look further into this.

If you look at the sheet "APRIL RAW DATA" each day of the month has its own set of hours.

For example 1-April-2014 has the following hours:
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

I need to match up the corresponding value for each hour on the other sheet "TEAM_1" sheet.

See attached for example - I filled in sample data for you to see

Download sample result: April1.xlsx

Originally Posted by :) Sixthsense :)

Oops.. sorry I missed to tell one more formula for source sheet step...

Refer the attached file for Revised Solutiion

Can you recheck your formula it still doesn't add up. For example on RAW DATA sheet for 5-April, 6-April and 12-April, their is no data, its blank for all hours but in the SUMMARY Sheet your formula is producing the number 8?? where is 8 coming from? it should be blank..

I think you are on the right track but could be some minor issue that needs correction.

I really hope you will be able to continue to fix this.

thank you kindly

Hello,

I added some dummy data, please look at the TEAM_1 SHEET and you will see the problem.. the result seems mostly accurate but then their are some days where the hours value is wrong. It shows value of 7797 ?? It should be 0 or blank if there is no match - see attached

Download: Revised_Solution_April.xlsx

I think there may be some small adjustment to formula to fix this..

I look forward to your final support

Working perfectly now! I cannot thank you enough!

I would love to give you reputation points but it doesn't let me, it says "thread does not have enough reputation".

dhan'yavāda