Index() Match() V/HLookup??? CHUGE Challenge! Up for it!? Need HELP!

I will do my best to explain – for me, it helps to know the what behind the why – so I apologize of this is too much, I have created a monthly KPI scorecard, that our Agents are scored on based on how many cases they resolve/close within a certain amount of time.

Example

Grand Total = Total Cases opened (count all cases that have an Age of 0 to 38 days)
Resolved Same Day (RSD) Age = 0
Resolved First Call(RFC) Age = 0
Resolved within 4 days or less age = < 4
Resolved within 8 days or less age = < 8

I also need to lookup how many cases were statues closed as "Resolved First Call" IF age = 0.. I know is pretty complicated, and I don't have backend access to the Database so I can’t do this in the SQL Code; so this is how I get it.. is it even possible??? - i am thinking this will require a complex VBA code..



I need to for each Tech Support agent, to count the amount of cases resolved/closed in <1, <4, <8; this is not ‘equal or less than X’ this is simply less than, so <8 = cases closed 0 to 7 days.
Seeing how the data is pulled, how I can get this? I’ve tried Pivot tables; Vlookup, Hlookup, Index, and match.
I need to be able to dump this data in, and do some type of lookup that pulls the data into their scorecard.

Looking at the attached file SFI data tab is the raw table. The SocreCard Tab has the format. I need to pull in the above criteria into their respective columns. The data pull is stupid, "Garbage in, Garbage out"

If anyone has any ideas, that would make this a bit easier than manually typing this in, it would be awesome..



I need to for each Tech Support agent, to count the amount of cases resolved/closed in <1, <4, <8; this is not ‘equal or less than X’ this is simply less than, so <8 = cases closed 0 to 7 days.
Seeing how the data is pulled, how I can get this? I’ve tried Pivot tables; Vlookup, Hlookup, Index, and match.
I need to be able to dump this data in, and do some type of lookup that pulls the data into their scorecard.

Looking at the attached file SFI data tab is the raw table. The SocreCard Tab has the format. I need to pull in the above critera into their respective colums. The data pull is stupid, "Garbage in, Garbage out"

If anyone has any ideas, that would make this a bit easier than manullay typing this in, it would be awesome.


Note*... On the SFI Data Tab, the ONLY columns I care about are the "Created by","Status","Age (Days)"