Counting Projects not working as intended

[PivotMeThis]

I have a list of projects that contains hours worked by employees. The project number is listed multiple times for each employee that charged time on it. Some employees work in the construction department and some work in the maintenance department.

To get my count of projects I used =IF(SUMPRODUCT(($D$2:$D2=D2)*($C$2:$C2=C2))>1,0,1) (D being Project Number and C being the District where the project was located).

To get my count of employees I used =IF(SUMPRODUCT(($F$2:$F2=F2)*($C$2:$C2=C2))>1,0,1) (F being the Employee Name and C being the District where the project was located).

I also have a column to list whether the employee works for construction and maintenance. =IF(I2=52,"Construction","Maintenance") (I being the employee cost center)

I am pretty confident that my count projects and count employees is working but not so much when it comes to sorting out the construction/maintenance issue. I made a pivot table that shows construction/maintenance employees charging time to projects and it doesn't look right.

I only want to count the projects once and I only want to count the employees once but I need to know how many from each department are charging time. I also need to know how many maintenance employees worked on projects without construction staff charging time (that seems to be another kettle of worms though).

I am not convinced this is working properly because if both construction and maintenance are listed as having charged time the count project ends up with maintenance.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">RCEs District</td><td style=";">Project Number</td><td style="text-align: center;;">Count Projects</td><td style=";">Employee Name</td><td style="text-align: center;;">Employees</td><td style="text-align: center;;">Cost Center</td><td style="text-align: center;;">Prefix</td><td style="text-align: center;;">Const/Maint</td><td style="text-align: center;;">Labor Hours</td><td style="text-align: center;;">Vehicle Miles</td></tr><tr ><td style="color: #161120;text-align: center;">75</td><td style="text-align: center;;">3</td><td style=";">BRF-020-2(49)--38-81</td><td style="text-align: center;;">1</td><td style=";">KERRY D. JOHNSON</td><td style="text-align: center;;">1</td><td style="text-align: center;;">553804</td><td style="text-align: center;;">55</td><td style="text-align: center;;">Maintenance</td><td style="text-align: center;;">10</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">76</td><td style="text-align: center;;">3</td><td style=";">BRF-020-2(49)--38-81</td><td style="text-align: center;;">0</td><td style=";">BRAD PHIPPS</td><td style="text-align: center;;">0</td><td style="text-align: center;;">523300</td><td style="text-align: center;;">52</td><td style="text-align: center;;">Construction</td><td style="text-align: center;;">5</td><td style="text-align: center;;">187</td></tr><tr ><td style="color: #161120;text-align: center;">77</td><td style="text-align: center;;">3</td><td style=";">BRF-020-2(49)--38-81</td><td style="text-align: center;;">0</td><td style=";">MICHAEL G. ZEIMEN</td><td style="text-align: center;;">0</td><td style="text-align: center;;">523300</td><td style="text-align: center;;">52</td><td style="text-align: center;;">Construction</td><td style="text-align: center;;">30</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">78</td><td style="text-align: center;;">2</td><td style=";">BRF-024-1(12)--38-19</td><td style="text-align: center;;">1</td><td style=";">ROBERT P BECKMAN II</td><td style="text-align: center;;">0</td><td style="text-align: center;;">522400</td><td style="text-align: center;;">52</td><td style="text-align: center;;">Construction</td><td style="text-align: center;;">10</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">79</td><td style="text-align: center;;">2</td><td style=";">BRF-024-1(13)--38-19</td><td style="text-align: center;;">1</td><td style=";">ROBERT P BECKMAN II</td><td style="text-align: center;;">0</td><td style="text-align: center;;">522400</td><td style="text-align: center;;">52</td><td style="text-align: center;;">Construction</td><td style="text-align: center;;">7.7</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">80</td><td style="text-align: center;;">2</td><td style=";">BRF-024-1(16)--38-19</td><td style="text-align: center;;">1</td><td style=";">ROBERT P BECKMAN II</td><td style="text-align: center;;">0</td><td style="text-align: center;;">522400</td><td style="text-align: center;;">52</td><td style="text-align: center;;">Construction</td><td style="text-align: center;;">16.8</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">81</td><td style="text-align: center;;">2</td><td style=";">BRF-024-2(5)--38-96</td><td style="text-align: center;;">1</td><td style=";">RYAN MASTERS</td><td style="text-align: center;;">0</td><td style="text-align: center;;">552807</td><td style="text-align: center;;">55</td><td style="text-align: center;;">Maintenance</td><td style="text-align: center;;">8</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">82</td><td style="text-align: center;;">2</td><td style=";">BRF-024-2(5)--38-96</td><td style="text-align: center;;">0</td><td style=";">ARLENE MC CUMBER</td><td style="text-align: center;;">0</td><td style="text-align: center;;">522400</td><td style="text-align: center;;">52</td><td style="text-align: center;;">Construction</td><td style="text-align: center;;">3</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">83</td><td style="text-align: center;;">2</td><td style=";">BRF-024-2(5)--38-96</td><td style="text-align: center;;">0</td><td style=";">DENNIS J MARQUART</td><td style="text-align: center;;">0</td><td style="text-align: center;;">522400</td><td style="text-align: center;;">52</td><td style="text-align: center;;">Construction</td><td style="text-align: center;;">19</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">84</td><td style="text-align: center;;">2</td><td style=";">BRF-024-2(5)--38-96</td><td style="text-align: center;;">0</td><td style=";">GARY L GRINNA</td><td style="text-align: center;;">1</td><td style="text-align: center;;">522400</td><td style="text-align: center;;">52</td><td style="text-align: center;;">Construction</td><td style="text-align: center;;">143</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">85</td><td style="text-align: center;;">2</td><td style=";">BRF-024-2(5)--38-96</td><td style="text-align: center;;">0</td><td style=";">GLENN D UGLUM</td><td style="text-align: center;;">1</td><td style="text-align: center;;">522400</td><td style="text-align: center;;">52</td><td style="text-align: center;;">Construction</td><td style="text-align: center;;">50.6</td><td style="text-align: center;;">655</td></tr><tr ><td style="color: #161120;text-align: center;">86</td><td style="text-align: center;;">2</td><td style=";">BRF-024-2(5)--38-96</td><td style="text-align: center;;">0</td><td style=";">STEPHEN L ARMSTRONG</td><td style="text-align: center;;">0</td><td style="text-align: center;;">522400</td><td style="text-align: center;;">52</td><td style="text-align: center;;">Construction</td><td style="text-align: center;;">24.6</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">87</td><td style="text-align: center;;">2</td><td style=";">BRF-024-2(6)--38-96</td><td style="text-align: center;;">1</td><td style=";">STEVEN L TIMMERMAN</td><td style="text-align: center;;">1</td><td style="text-align: center;;">522400</td><td style="text-align: center;;">52</td><td style="text-align: center;;">Construction</td><td style="text-align: center;;">19.6</td><td style="text-align: center;;">0</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">2008</p><br /><br />

The table below shows that in District 1, 43 construction employees charged time to 107 projects and that 21 maintenance employees charged time to 78 projects. This is what I don't think is accurate because both construction and maintenance employees are charging time to the same projects but maintenance is being counted on those alone.

I am thinking that the 107 projects must be projects where only construction charged time. (Because of the way the data looks to me). Is there a better way to do this? Is there a way I can find out how many maintenance employees charged time on projects in which construction did not charge time?

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-left: 1px solid black;;">Work Accomplishment</td><td style="font-weight: bold;text-align: center;;">Labor Hours </td><td style="font-weight: bold;text-align: center;;">Hours / $100,000</td><td style="font-weight: bold;text-align: center;;">Vehicle Miles </td><td style="font-weight: bold;text-align: center;;">Miles / $100,000</td><td style="font-weight: bold;text-align: center;;">Employees </td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;;">Projects</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-right: 1px solid black;;">Construction</td><td style="border-left: 1px solid black;;"></td><td style="text-align: right;;">48,325.2</td><td style="text-align: right;;">52.3</td><td style="text-align: right;;">354,007</td><td style="text-align: right;;">383</td><td style="text-align: center;;">42</td><td style="text-align: center;border-right: 1px solid black;;">107</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-right: 1px solid black;;">Maintenance</td><td style="border-left: 1px solid black;;"></td><td style="text-align: right;;">18,007.4</td><td style="text-align: right;;">19.5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: center;;">22</td><td style="text-align: center;border-right: 1px solid black;;">78</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;border-right: 1px solid black;;">District 1</td><td style="font-weight: bold;text-align: right;border-left: 1px solid black;;">$92,400,000</td><td style="font-weight: bold;text-align: right;;">66,332.6</td><td style="font-weight: bold;text-align: right;;">71.8</td><td style="font-weight: bold;text-align: right;;">354,007</td><td style="font-weight: bold;text-align: right;;">383</td><td style="font-weight: bold;text-align: center;;">64</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;;">185</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-right: 1px solid black;;">Construction</td><td style="border-left: 1px solid black;;"></td><td style="text-align: right;;">35,093.3</td><td style="text-align: right;;">78.0</td><td style="text-align: right;;">366,680</td><td style="text-align: right;;">815</td><td style="text-align: center;;">25</td><td style="text-align: center;border-right: 1px solid black;;">119</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-right: 1px solid black;;">Maintenance</td><td style="border-left: 1px solid black;;"></td><td style="text-align: right;;">12,048.7</td><td style="text-align: right;;">26.8</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: center;;">28</td><td style="text-align: center;border-right: 1px solid black;;">55</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;border-right: 1px solid black;;">District 2</td><td style="font-weight: bold;text-align: right;border-left: 1px solid black;;">$45,000,000</td><td style="font-weight: bold;text-align: right;;">47,142.0</td><td style="font-weight: bold;text-align: right;;">104.8</td><td style="font-weight: bold;text-align: right;;">366,680</td><td style="font-weight: bold;text-align: right;;">815</td><td style="font-weight: bold;text-align: center;;">53</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;;">174</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-right: 1px solid black;;">Construction</td><td style="border-left: 1px solid black;;"></td><td style="text-align: right;;">43,897.9</td><td style="text-align: right;;">74.4</td><td style="text-align: right;;">466,123</td><td style="text-align: right;;">790</td><td style="text-align: center;;">36</td><td style="text-align: center;border-right: 1px solid black;;">101</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-right: 1px solid black;;">Maintenance</td><td style="border-left: 1px solid black;;"></td><td style="text-align: right;;">20,128.3</td><td style="text-align: right;;">34.1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: center;;">43</td><td style="text-align: center;border-right: 1px solid black;;">61</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;border-right: 1px solid black;;">District 3</td><td style="font-weight: bold;text-align: right;border-left: 1px solid black;;">$59,000,000</td><td style="font-weight: bold;text-align: right;;">64,026.2</td><td style="font-weight: bold;text-align: right;;">108.5</td><td style="font-weight: bold;text-align: right;;">466,123</td><td style="font-weight: bold;text-align: right;;">790</td><td style="font-weight: bold;text-align: center;;">79</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;;">162</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="border-right: 1px solid black;;">Construction</td><td style="border-left: 1px solid black;;"></td><td style="text-align: right;;">43,603.9</td><td style="text-align: right;;">49.5</td><td style="text-align: right;;">353,548</td><td style="text-align: right;;">402</td><td style="text-align: center;;">30</td><td style="text-align: center;border-right: 1px solid black;;">104</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="border-right: 1px solid black;;">Maintenance</td><td style="border-left: 1px solid black;;"></td><td style="text-align: right;;">19,135.1</td><td style="text-align: right;;">21.7</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: center;;">45</td><td style="text-align: center;border-right: 1px solid black;;">58</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="font-weight: bold;border-right: 1px solid black;;">District 4</td><td style="font-weight: bold;text-align: right;border-left: 1px solid black;;">$88,000,000</td><td style="font-weight: bold;text-align: right;;">62,739.0</td><td style="font-weight: bold;text-align: right;;">71.3</td><td style="font-weight: bold;text-align: right;;">353,548</td><td style="font-weight: bold;text-align: right;;">402</td><td style="font-weight: bold;text-align: center;;">75</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;;">162</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Summary</p><br /><br />