I have a spread sheet which monitors works on several sites. Issues are normally passed to me for each site once. They are split into Issue types eg, building, Misc, A/C etc. Each Issue can be completed independent of the other issues for a particular site.
Columns of concerns are
C: = Site
F: =Issues
G: =Issue Type
O: Date Completed
P: Passed to Completed Team
I need to gather stats on a weekly and a monthly basis. As follows:
No. of Sites Received:
No. of Sites in Backlog:
No. of Sites Completed:
Since I have over 500 sites, it is rather difficult to count the number of sites completed. The easiest way I get around it is to filter on Date completed column. Make a not of sites completed within a period -using custom filter on column O. Next I take the filter off O and look for blanks on column again. To eliminate them from the list I made.
I have the same difficulty recording sites received. If a site has been received with issues already and ALL Issues are not completed before another new issue is received then, that issue is not classed in the stats as received for the week/month.
To gather the number of sites is the backlog it is much easier but I have to do a manual count after I have enabled filter on column O (blanks)
Is there a way to see the number of unique site when filters are enabled?
At present, since several issues can be completed within a period and these issues can be related to a one site my count is incorrect. The count shows the number of issues rather than site.
Secondly, is there a better way of gathering stats using filters or not in excel for my issues tracker?
Thanks for your assistance
Start Free Trial
