Counting Projects not working as intended

PivotMeThis

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

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,101
Messages
5,835,389
Members
430,354
Latest member
Novice125

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