We have a problem I am at a loss with. In one tab, we have a list of people who completed a document, we'll call it the ROAR. Everytime the ROAR was completed, the person who did it was added to this list. I have another tab with a formula to count the amount of people who completed the ROAR, but once they have been counted, ignore the other times. Essentially, it's trying to find out how many people were involved in completing the ROAR, rather than the amount of times it was completed.
The current formula we have going to do this is: {=ROUNDUP(SUM((1/COUNTIF(Data!$A$2382:$A$4927,Data!$A$2382:$A$4927))*IF(Data!$C$2382:$C$4927=TAB!$C4,IF(Data!$E$2382:$E$4927>=TAB!$D$2,IF(Data!$E$2382:$E$4927<=TAB!$E$2,IF(Data!$D$2382:$D$4927=TAB!D$3,1,0),0),0),0)),0)}
TAB is where the formula is, Data is where the names are.
I am aware COUNTIF is the incorrect formula, as 'IF(FREQUENCY(MATCH('would be much better, but due to running on 2003, after a few hundred values in the calculation it throws up a bunch of #N/A's which results in an overall #N/A answer.
The IF statements after are checking (in order) for the name of the team the person is in which is listed in C4, the date range its looking within, D2-E2, and their job title listed in D3.
Please help. I am at a complete loss.
Thank you in advance
Aucun commentaire:
Enregistrer un commentaire