# Count number of employees within several different differentials

#### bh24524

##### Active Member
I am trying to figure out how many employees get several different shift differentials or premiums based on the following list:

The first column lists the department code where the third and 4th columns list if that department gets both nite rate and cooler rate premium. As you see, some dept.s only get cooler, some only get nite, but some get both cooler and nite.

On a separate Tab called NEW BID in column G starting in row 5 with the first employee, is where it contains the departments per above list that employees are designated.

I'd like to do a count of how many employees are receiving cooler rate, how many are receiving nite rate, and how many are receiving BOTH cooler and nite rate. I was going to have those counts in 3 separate cells. What formulas could I use based on this info?

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### JamesCanale

##### Active Member

Where \$A\$1:\$D\$10 is the department code list on the first sheet.

 =SUMPRODUCT(--(VLOOKUP(G5:G18,\$A\$1:\$D\$10,3,FALSE)="Nite")) =SUMPRODUCT(--(VLOOKUP(G5:G18,\$A\$1:\$D\$10,4,FALSE)="Cooler")) =SUMPRODUCT(--(VLOOKUP(G5:G18,\$A\$1:\$D\$10,4,FALSE)="Cooler"),--(VLOOKUP(G5:G18,\$A\$1:\$D\$10,3,FALSE)="Nite"))

#### bh24524

##### Active Member

Where \$A\$1:\$D\$10 is the department code list on the first sheet.

 =SUMPRODUCT(--(VLOOKUP(G5:G18,\$A\$1:\$D\$10,3,FALSE)="Nite")) =SUMPRODUCT(--(VLOOKUP(G5:G18,\$A\$1:\$D\$10,4,FALSE)="Cooler")) =SUMPRODUCT(--(VLOOKUP(G5:G18,\$A\$1:\$D\$10,4,FALSE)="Cooler"),--(VLOOKUP(G5:G18,\$A\$1:\$D\$10,3,FALSE)="Nite"))
Hi, thanks for the reply. So I tried out the second one with just cooler but it is yielding zero:

Excel Formula:
``=SUMPRODUCT(--(VLOOKUP('NEW BID'!G5:G300,\$P\$2:S\$16,4,FALSE)="Cooler"))``

P2 on the First sheet which is called "Shift Info" is what starts my first department and S16 contains cooler for my last dept. C67GJ. on the New Bid Tab, I just did G5 thru G300(there aren't that many but I'm just allowing for possible expansion)

Did I get something wrong in the formula?

#### JamesCanale

##### Active Member
I can't see a problem with the formula. I'd start taking the formula apart. Try the vlookup (without the sumproduct) on each row of the 'new bid' sheet to see if that is working. Maybe there is an odd space on the department name or it could be an oddity with the word 'cooler' in 'shift info' sheet.

The whole thing is working quite nicely on my sheets. Columns H and I are just to check the results.

Book1 (version 1).xlsb
ABCDEFGHI
1c61c565
2c61cj65410
3c61q45Nite3
4c61qj12Nite
5c65d654Coolerjohn1c65d0Cooler
6c65dj23Coolerjohn2c65d0Cooler
7c65f23Coolerjohn3c65f0Cooler
8c65fj23Coolerjohn4c65d0Cooler
9c65l23NiteCoolerjohn5c65lkNiteCooler
10c65lk12NiteCoolerjohn6c61c00
11john7c61cj00
12john8c61qNite0
13john9c61qNite0
14john10c65dj0Cooler
15john11c65lNiteCooler
16john12c65f0Cooler
17john13c65fj0Cooler
18john14c65lNiteCooler
DeptSheet
Cell Formulas
RangeFormula
G1G1=SUMPRODUCT(--(VLOOKUP(G5:G18,OtherSheet!A1:D10,3,FALSE)="Nite"))
G2G2=SUMPRODUCT(--(VLOOKUP(G5:G18,OtherSheet!A1:D10,4,FALSE)="Cooler"))
G3G3=SUMPRODUCT(--(VLOOKUP(G5:G18,OtherSheet!A1:D10,4,FALSE)="Cooler"),--(VLOOKUP(G5:G18,OtherSheet!A1:D10,3,FALSE)="Nite"))
H5:H18H5=VLOOKUP(G5,\$A\$1:\$D\$10,3,FALSE)
I5:I18I5=VLOOKUP(G5,\$A\$1:\$D\$10,4,FALSE)

#### bh24524

##### Active Member

I'm sorry, but I've checked my formula, and I have everything exactly how I believe I should have it. I have the formula for counting "cooler" in the "Shift Info" Tab which also contains the table of departments and which ones get nite and which ones get cooler.
Here is a screenshot of the Shift Info Tab:

Excel Formula:
``=SUMPRODUCT(--(VLOOKUP('NEW BID'!G5:G300,'SHIFT INFO'!P2:S16,4,FALSE)="Cooler"))``

The "New Bid" tab pictured below is what has the listing of departments for each employee, although I don't know if it makes a difference or not, but that column that contains those departments is formula driven to yield those departments via lookup. I wouldn't think that should affect anything.

I can see that yours does work but I just can't see where mine doesn't.

#### bh24524

##### Active Member
I tried the formula with just the Vlookup on the New Bid Tab and it was correctly coding the cooler departments as True while coding the non-cooler departments as False. @Tetra201 Could you maybe offer some assistance with this or perhaps an alternative solution?

#### JamesCanale

##### Active Member

Maybe it's an excel version problem.

#### Fluff

##### MrExcel MVP, Moderator
+Fluff 1.xlsm
ABCDEFG
1c61c56Nite2
2c61cj654Cooler7
3c61q45NiteBoth3
4c61qj12Nite
5c65d654Coolerjohn1c65d
6c65dj23Coolerjohn2c65d
7c65f23Coolerjohn3c65f
8c65fj23Coolerjohn4c65d
9c65l23NiteCoolerjohn5c65lk
10c65lk12NiteCoolerjohn6c61c
11john7c61cj
12john8c61q
13john9c61q
14john10c65dj
15john11c65l
16john12c65f
17john13c65fj
18john14c65l
Master
Cell Formulas
RangeFormula
G1G1=SUM(COUNTIFS(A1:A10,G5:G18,C1:C10,"<>",D1:D10,""))
G2G2=SUM(COUNTIFS(A1:A10,G5:G18,C1:C10,"",D1:D10,"<>"))
G3G3=SUM(COUNTIFS(A1:A10,G5:G18,C1:C10,"<>",D1:D10,"<>"))

#### bh24524

##### Active Member
+Fluff 1.xlsm
ABCDEFG
1c61c56Nite2
2c61cj654Cooler7
3c61q45NiteBoth3
4c61qj12Nite
5c65d654Coolerjohn1c65d
6c65dj23Coolerjohn2c65d
7c65f23Coolerjohn3c65f
8c65fj23Coolerjohn4c65d
9c65l23NiteCoolerjohn5c65lk
10c65lk12NiteCoolerjohn6c61c
11john7c61cj
12john8c61q
13john9c61q
14john10c65dj
15john11c65l
16john12c65f
17john13c65fj
18john14c65l
Master
Cell Formulas
RangeFormula
G1G1=SUM(COUNTIFS(A1:A10,G5:G18,C1:C10,"<>",D1:D10,""))
G2G2=SUM(COUNTIFS(A1:A10,G5:G18,C1:C10,"",D1:D10,"<>"))
G3G3=SUM(COUNTIFS(A1:A10,G5:G18,C1:C10,"<>",D1:D10,"<>"))
Unfortunately I'm not getting results with that one either. Here is what I did again trying for cooler rate:

From the Shift Info Tab:

Data Starts in P2(C61C) and ends in S16(Cooler)

Excel Formula:
``=SUM(COUNTIFS(P2:P16,'NEW BID'!G5:G300,R2:R16,"",S2:S16,"<>"))``

From the New Bid Tab:
Data Starts in G5

The formula pictured is what is contained in G5 of the New Bid Tab and so forth. Again, I see that yours works, but I can't understand why mine wouldn't. As far as I can tell, I adjusted mine to pull from the correct sections.

I don't understand what could be causing this. Surely the fact that its on 2 different tabs shouldn't matter, right?

#### Fluff

##### MrExcel MVP, Moderator
Try confirming my suggestion with Ctrl Shift Enter, rather than just Enter.

Replies
5
Views
224
Replies
3
Views
271
Replies
3
Views
161
Replies
0
Views
65
Replies
3
Views
333

1,148,334
Messages
5,746,158
Members
423,995
Latest member
excelbloggs

### 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.

### Which adblocker are you using?

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

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