Counting Projects not working as intended

PivotMeThis

Active Member
Joined
Jun 24, 2002
Messages
341
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 />
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top