Count number of employees within several different differentials

[bh24524]

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?

 

[JamesCanale]

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]

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:

=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]

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
	A	B	C	D	E	F	G	H	I
1	c61c	56					5
2	c61cj	654					10
3	c61q	45	Nite				3
4	c61qj	12	Nite
5	c65d	654		Cooler		john1	c65d	0	Cooler
6	c65dj	23		Cooler		john2	c65d	0	Cooler
7	c65f	23		Cooler		john3	c65f	0	Cooler
8	c65fj	23		Cooler		john4	c65d	0	Cooler
9	c65l	23	Nite	Cooler		john5	c65lk	Nite	Cooler
10	c65lk	12	Nite	Cooler		john6	c61c	0	0
11						john7	c61cj	0	0
12						john8	c61q	Nite	0
13						john9	c61q	Nite	0
14						john10	c65dj	0	Cooler
15						john11	c65l	Nite	Cooler
16						john12	c65f	0	Cooler
17						john13	c65fj	0	Cooler
18						john14	c65l	Nite	Cooler
DeptSheet

Cell Formulas
Range	Formula
G1	G1	=SUMPRODUCT(--(VLOOKUP(G5:G18,OtherSheet!A1:D10,3,FALSE)="Nite"))
G2	G2	=SUMPRODUCT(--(VLOOKUP(G5:G18,OtherSheet!A1:D10,4,FALSE)="Cooler"))
G3	G3	=SUMPRODUCT(--(VLOOKUP(G5:G18,OtherSheet!A1:D10,4,FALSE)="Cooler"),--(VLOOKUP(G5:G18,OtherSheet!A1:D10,3,FALSE)="Nite"))
H5:H18	H5	=VLOOKUP(G5,$A$1:$D$10,3,FALSE)
I5:I18	I5	=VLOOKUP(G5,$A$1:$D$10,4,FALSE)

 

[bh24524]

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:

=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]

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]

Maybe it's an excel version problem.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	c61c	56				Nite	2
2	c61cj	654				Cooler	7
3	c61q	45	Nite			Both	3
4	c61qj	12	Nite
5	c65d	654		Cooler		john1	c65d
6	c65dj	23		Cooler		john2	c65d
7	c65f	23		Cooler		john3	c65f
8	c65fj	23		Cooler		john4	c65d
9	c65l	23	Nite	Cooler		john5	c65lk
10	c65lk	12	Nite	Cooler		john6	c61c
11						john7	c61cj
12						john8	c61q
13						john9	c61q
14						john10	c65dj
15						john11	c65l
16						john12	c65f
17						john13	c65fj
18						john14	c65l
Master

Cell Formulas
Range	Formula
G1	G1	=SUM(COUNTIFS(A1:A10,G5:G18,C1:C10,"<>",D1:D10,""))
G2	G2	=SUM(COUNTIFS(A1:A10,G5:G18,C1:C10,"",D1:D10,"<>"))
G3	G3	=SUM(COUNTIFS(A1:A10,G5:G18,C1:C10,"<>",D1:D10,"<>"))

 

[bh24524]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	c61c	56				Nite	2
2	c61cj	654				Cooler	7
3	c61q	45	Nite			Both	3
4	c61qj	12	Nite
5	c65d	654		Cooler		john1	c65d
6	c65dj	23		Cooler		john2	c65d
7	c65f	23		Cooler		john3	c65f
8	c65fj	23		Cooler		john4	c65d
9	c65l	23	Nite	Cooler		john5	c65lk
10	c65lk	12	Nite	Cooler		john6	c61c
11						john7	c61cj
12						john8	c61q
13						john9	c61q
14						john10	c65dj
15						john11	c65l
16						john12	c65f
17						john13	c65fj
18						john14	c65l
Master

Cell Formulas
Range	Formula
G1	G1	=SUM(COUNTIFS(A1:A10,G5:G18,C1:C10,"<>",D1:D10,""))
G2	G2	=SUM(COUNTIFS(A1:A10,G5:G18,C1:C10,"",D1:D10,"<>"))
G3	G3	=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)

=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]

Try confirming my suggestion with Ctrl Shift Enter, rather than just Enter.