Count of temp and perm workers per month

Remsnow

New Member
Joined
Aug 19, 2015
Messages
44
Hi

Having used this board before I am sure this will be pretty simple for someone on here but I just can't figure it out!

I have the following columns; 'Working Month' (e.g. January 16), 'Membership Number', and 'Worker name', 'Agency Type' (Temp or Perm are the only two values,. On a summary page I want;

Column A: January 16, February, 16 etc
Column B: Temp (below the number of UNIQUE temp workers in that month)
Column C: Perm (below the number of UNIQUE perm workers in that month)

Is this possible please?

Any help is as always massively appreciated and will really help me at work!

Thanks!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Remsnow

New Member
Joined
Aug 19, 2015
Messages
44
I understand that the version of excel may make a difference. I am using excel 2007

Thanks
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
deleted.

more tests required
 
Last edited:

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
ok, try this

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><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: rgb(22,17,32);text-align: center;">1</td><td style=";">Working Month</td><td style=";">Membership Number</td><td style=";">Worker name</td><td style=";">Agency Type</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Temp</td><td style=";">Perm</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">January-16</td><td style="text-align: right;;">102</td><td style="text-align: right;;"></td><td style=";">Perm</td><td style="text-align: right;;"></td><td style="text-align: right;;">January-16</td><td style="text-align: right;background-color: #E2EFDA;;">3</td><td style="text-align: right;background-color: #E2EFDA;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">January-16</td><td style="text-align: right;;">102</td><td style="text-align: right;;"></td><td style=";">Temp</td><td style="text-align: right;;"></td><td style="text-align: right;;">February-16</td><td style="text-align: right;background-color: #E2EFDA;;">4</td><td style="text-align: right;background-color: #E2EFDA;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">January-16</td><td style="text-align: right;;">101</td><td style="text-align: right;;"></td><td style=";">Temp</td><td style="text-align: right;;"></td><td style="text-align: right;;">March-16</td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;background-color: #E2EFDA;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">January-16</td><td style="text-align: right;;">104</td><td style="text-align: right;;"></td><td style=";">Temp</td><td style="text-align: right;;"></td><td style="text-align: right;;">April-16</td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;background-color: #E2EFDA;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">January-16</td><td style="text-align: right;;">104</td><td style="text-align: right;;"></td><td style=";">Temp</td><td style="text-align: right;;"></td><td style="text-align: right;;">May-16</td><td style="text-align: right;background-color: #E2EFDA;;">2</td><td style="text-align: right;background-color: #E2EFDA;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">January-16</td><td style="text-align: right;;">101</td><td style="text-align: right;;"></td><td style=";">Temp</td><td style="text-align: right;;"></td><td style="text-align: right;;">June-16</td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;background-color: #E2EFDA;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">January-16</td><td style="text-align: right;;">105</td><td style="text-align: right;;"></td><td style=";">Perm</td><td style="text-align: right;;"></td><td style="text-align: right;;">July-16</td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;background-color: #E2EFDA;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">January-16</td><td style="text-align: right;;">104</td><td style="text-align: right;;"></td><td style=";">Perm</td><td style="text-align: right;;"></td><td style="text-align: right;;">August-16</td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;background-color: #E2EFDA;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">February-16</td><td style="text-align: right;;">101</td><td style="text-align: right;;"></td><td style=";">Temp</td><td style="text-align: right;;"></td><td style="text-align: right;;">September-16</td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;background-color: #E2EFDA;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">February-16</td><td style="text-align: right;;">105</td><td style="text-align: right;;"></td><td style=";">Temp</td><td style="text-align: right;;"></td><td style="text-align: right;;">October-16</td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;background-color: #E2EFDA;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">February-16</td><td style="text-align: right;;">102</td><td style="text-align: right;;"></td><td style=";">Temp</td><td style="text-align: right;;"></td><td style="text-align: right;;">November-16</td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;background-color: #E2EFDA;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">February-16</td><td style="text-align: right;;">104</td><td style="text-align: right;;"></td><td style=";">Perm</td><td style="text-align: right;;"></td><td style="text-align: right;;">December-16</td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;background-color: #E2EFDA;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">February-16</td><td style="text-align: right;;">102</td><td style="text-align: right;;"></td><td style=";">Perm</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">February-16</td><td style="text-align: right;;">105</td><td style="text-align: right;;"></td><td style=";">Temp</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">February-16</td><td style="text-align: right;;">104</td><td style="text-align: right;;"></td><td style=";">Temp</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">May-16</td><td style="text-align: right;;">102</td><td style="text-align: right;;"></td><td style=";">Perm</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">May-16</td><td style="text-align: right;;">102</td><td style="text-align: right;;"></td><td style=";">Temp</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">May-16</td><td style="text-align: right;;">104</td><td style="text-align: right;;"></td><td style=";">Temp</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">May-16</td><td style="text-align: right;;">103</td><td style="text-align: right;;"></td><td style=";">Perm</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">May-16</td><td style="text-align: right;;">104</td><td style="text-align: right;;"></td><td style=";">Perm</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">May-16</td><td style="text-align: right;;">101</td><td style="text-align: right;;"></td><td style=";">Perm</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">{=SUM(<font color="Blue">--(<font color="Red">FREQUENCY(<font color="Green">IF(<font color="Purple">$A$2:$A$22=$F2,IF(<font color="Teal">$D$2:$D$22=G$1,$B$2:$B$22</font>)</font>),$B$2:$B$22</font>)>0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,128,107
Messages
5,628,731
Members
416,333
Latest member
Time2Learn

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