Count values with reference from a cell on a different worksheet

[mcoetzee]

I'm trying to count the number of shifts of employees by looking up by employee. The shift data is on a seperate worksheet. I would like to use a formula that refers to the cell for each employee and then finds the corresponding name and counts the number of shifts in the rows and columns on the next worksheet. I hope this makes sense.

Count problem.xlsx
	A	B
1		Total number of shifts
2	Employee 1
3	Employee 2
4	Employee 3
5	Employee 4
6	Employee 5
Datasheet1

Count problem.xlsx
	A	B	C	D	E	F	G
1	Row Labels	Monday	Tuesday	Wednesday	Thursday	Friday	Grand Total
2	Employee 1	7.5		7.5	7.5	7.5	30
3	Employee 2		7.5	7.5	7.5	7.5	30
4	Employee 3	7.5	7.5	7.5	7.5	7.5	37.5
5	Employee 4	7.5	7.5	7.5	7.5	7.5	37.5
6	Employee 5	7.5	7.5	7.5	7.5	7.5	37.5
Datasheet 2

 

[maabadi]

Try this:

Book1
	A	B	C
1		Total number of shifts
2	Employee 1	4
3	Employee 2	4
4	Employee 3	5
5	Employee 4	5
6	Employee 5	5
7
DataSheet1

Cell Formulas
Range	Formula
B2:B6	B2	=SUMPRODUCT(--(DataSheet2!$A$2:$A$6=A2)*--(DataSheet2!$B$2:$F$6>1))

 

[mcoetzee]

Try this:

Book1
	A	B	C
1		Total number of shifts
2	Employee 1	4
3	Employee 2	4
4	Employee 3	5
5	Employee 4	5
6	Employee 5	5
7
DataSheet1

Cell Formulas
Range	Formula
B2:B6	B2	=SUMPRODUCT(--(DataSheet2!$A$2:$A$6=A2)*--(DataSheet2!$B$2:$F$6>1))

worked a charm. Thank you!

 

[maabadi]

You're Welcome & Thanks for follow-up.

 

[Peter_SSs]

@mcoetzee

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

IF you have MS365 you could also do it like this

21 09 20.xlsm
	A	B
1		Total number of shifts
2	Employee 1	4
3	Employee 2	4
4	Employee 3	5
5	Employee 4	5
6	Employee 5	5
Datasheet1

Cell Formulas
Range	Formula
B2:B6	B2	=COUNT(FILTER(Datasheet2!B$2:F$6,Datasheet2!A$2:A$6=A2,""))