Look up with 2 criteria

[EXCELBENCH]

Hi,

I'm working on an Productivity Tracker.
This tracker includes OT Hours, Reward Amount, Records processed and the name of the Agents.
If the agent hits the 30 OT Hours he will have the chance to get a reward amount depending on his records processed.
In the example below.
She got the 30 hours by Sep 15 and the 150 records processed is equivalent to P1,500. (which is the 1st occurrence of 30 hours)
The next reward amount she will get is the 2nd occurrence of 30 hours.
As shown on the image. She has a total of 5 occurrences of 30 hours which entitles her to received a reward amount per occurrence.
Incentive Card.JPG

The 'Tracker' Sheet is already good and finished but my problem now is how to get the sum of the agent's OT Hours, Records processed and Reward Amount by OCCURRENCE and on the below table by TOTAL.
SummaryReportOfProductivityDrive.JPG

Please help me work on this summary. I'm already exhausted on working on the Tracker sheet. I know I asked a lot in this forum but this is really important for me.

THANK YOU I WILL VERY MUCH APPRECIATE IT.

Here's the file:

[Richard Buttrey]

You're probably not going to welcome this but I suspect you've boxed yourself into a corner with your layout.
With any sort of database exercise like this you should have kept all your data in a normalised two dimensional table. In your case that would have consisted of a column for the persons name and then the three data columns.

With that sort of layout you'd then normally use a Pivot table to produce the stuff you're wanting for your dashboard. You may need to use calculated fields within the PT or perhaps helper columns as you have now.

So personally I'd try that approach. I don't understand the rules you use to calculate your records processed and reward amount so if you can explain those in a narrative text we may be able to offer a neater solution.

[EXCELBENCH]

I understand Sir. Actually the link below is my first layout but I'm having problem getting the data for each 30 hours occurrence and this is the only way I thought of adding the excess hours and records to the next 30 hours.

Sir please visit this link you can probably help me on this kind of layout.

https://www.excelforum.com/excel-for...ther-data.html

[Richard Buttrey]

With 79 posts you should know that we have a rule about opening more than one thread for the same subject.
By doing so you waste the time of others.

I'm closing this thread