Hi, I wonder if an expert here could help me with a formula i've been struggling with for some time.
I am trying to create some statistics from data queried from our Ticketing system, and want to get a total number of Resolved cases per user, however if a case has a resolution cancelled, and then is resolved for a 2nd time, both resolves are counted in the statistics which can sometimes make it look like the least effective members of the team (who have their resolutions cancelled multiple times) are actually the most effective (highest number of resolves)
The relevant data in the spreadsheet is as follows:
The current report i am running, using a SUMPRODUCT would calculate 3 resolves for John Smith and 3 for Joe Bloggs but I would like it to show 0 for John Smith as all his resolutions were later cancelled.
I thought the easiest way would be to add the "FINAL RESOLVE?" column and have it state Yes or No if it is the last time there was a RESOLVE operation for that INCIDENT ID and then use that in my SUMPRODUCT instead of the OPERATION field but I can't figure out what formula I could use to achieve this.
Any help would be greatly appreciated.
Bookmarks