Count number of employees within several different differentials

bh24524

Active Member
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007
I am trying to figure out how many employees get several different shift differentials or premiums based on the following list:

1611682787201.png


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
Joined
Jan 13, 2021
Messages
393
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
How about:

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
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007
How about:

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"))

1611689599377.png


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
Joined
Jan 13, 2021
Messages
393
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007

ADVERTISEMENT

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:
1611752781776.png


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.

1611752720809.png


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

bh24524

Active Member
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007
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
Joined
Jan 13, 2021
Messages
393
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Maybe it's an excel version problem.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,043
Office Version
  1. 365
Platform
  1. Windows
How about
+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
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007
How about
+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)
1611775766001.png

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

From the New Bid Tab:
Data Starts in G5
1611775876550.png


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
Joined
Jun 12, 2014
Messages
66,043
Office Version
  1. 365
Platform
  1. Windows
Try confirming my suggestion with Ctrl Shift Enter, rather than just Enter.
 

Forum statistics

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