statistical statement for the number of each employee

[Mussa]

hello
I would count containing for each employee based on matching columns with the headers .

orginal data

1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2	days	date	employee1				employee2					bra1	bra2	bra3	bra4	sel1	sel2	sel3	sel4	mr	sr	vec	tr
3	sat	1	bra1	sel1	mr		bra2	sel1	mr		employee1
4	sun	2	bra1	sel1	mr		tr	sel1			employee2
5	mon	3	bra1	sel2	mr		vec				employee3
6	tue	4	bra2	sel3	sr		bra2	sel4	sr		employee4
7	wed	5	vec				bra4	sel4	mr		employee5
8	thu	6	bra1	sel1	mr		tr				employee6
9	fri	7	tr				bra3	sel2	sr		employee7
10											employee8
s

result

1.xlsx
	K	L	M	N	O	P	Q	R	S	T	U	V	W
2		bra1	bra2	bra3	bra4	sel1	sel2	sel3	sel4	mr	sr	vec	tr
3	employee1	4	1	0	0	3	1	1	0	4	1	1	1
4	employee2	0	2	1	1	2	1	0	2	2	2	1	2
5	employee3
6	employee4
7	employee5
8	employee6
9	employee7
10	employee8
s

 

[Peter_SSs]

Try this

22 02 28.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	days	date	employee1				employee2					bra1	bra2	bra3	bra4	sel1	sel2	sel3	sel4	mr	sr	vec	tr
2	sat	1	bra1	sel1	mr		bra2	sel1	mr		employee1	4	1	0	0	3	1	1	0	4	1	1	1
3	sun	2	bra1	sel1	mr		tr	sel1			employee2	0	2	1	1	2	1	0	2	2	2	1	2
4	mon	3	bra1	sel2	mr		vec				employee3
5	tue	4	bra2	sel3	sr		bra2	sel4	sr		employee4
6	wed	5	vec				bra4	sel4	mr		employee5
7	thu	6	bra1	sel1	mr		tr				employee6
8	fri	7	tr				bra3	sel2	sr		employee7
9											employee8
Count per Employee

Cell Formulas
Range	Formula
L2:W9	L2	=IFNA(COUNTIF(INDEX($C$2:$I$8,1,MATCH($K2,$C$1:$I$1,0)):INDEX($C$2:$I$8,ROWS($C$2:$I$8),MATCH($K2,$C$1:$I$1,0)+2),L$1),"")

 

[RasGhul]

Maybe this

Book1
	K	L	M	N	O	P	Q	R	S	T	U	V	W
1		bra1	bra2	bra3	bra4	sel1	sel2	sel3	sel4	mr	sr	vec	tr
2	employee1	4	1	0	0	3	1	1	0	4	1	1	1
3	employee2	0	2	1	1	2	1	0	2	2	2	1	2
4	employee3
Sheet1

Cell Formulas
Range	Formula
L2:W3	L2	=SUMPRODUCT(($C$1:$I$1=$K2)*($C$2:$I$8=L$1))

 

[RasGhul]

Maybe this

statistical statement for the number of each employee_Mussa.xlsx
	A	B	C	D	E	F	G	H	I
1	days	date	employee1	employee1	employee1		employee2	employee2	employee2
2	sat	1	bra1	sel1	mr		bra2	sel1	mr
3	sun	2	bra1	sel1	mr		tr	sel1
4	mon	3	bra1	sel2	mr		vec
5	tue	4	bra2	sel3	sr		bra2	sel4	sr
6	wed	5	vec				bra4	sel4	mr
7	thu	6	bra1	sel1	mr		tr
8	fri	7	tr				bra3	sel2	sr
Sheet1

Book1
	K	L	M	N	O	P	Q	R	S	T	U	V	W
1		bra1	bra2	bra3	bra4	sel1	sel2	sel3	sel4	mr	sr	vec	tr
2	employee1	4	1			3	1	1		4	1	1	1
3	employee2		2	1	1	2	1		2	2	2	1	2
4	employee3
Sheet1

Cell Formulas
Range	Formula
L2:W3	L2	=SUMPRODUCT(($C$1:$I$1=$K2)*($C$2:$I$8=L$1))

 

[Mussa]

magnificent! thanks guys . how can I expand the formula to include the days
so when count the days for each employee should be zero if the cells in columns D ,I togheter and H,I together are empty
like this

collect column.xlsM
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	days	date	employee1				employee2					bra1	bra2	bra3	bra4	sel1	sel2	sel3	sel4	mr	sr	vec	tr	sat	sun	mon	tue	wed	thu	fri
2	sat	1	bra1	sel1	mr		bra2	sel1	mr		employee1	4	1	0	0	3	1	1	0	4	1	1	1	1	1	1	1	0	1	0
3	sun	2	bra1	sel1	mr		tr	sel1			employee2	0	2	1	1	2	1	0	2	2	2	1	2	1	1	0	1	1	0	1
4	mon	3	bra1	sel2	mr		vec				employee3
5	tue	4	bra2	sel3	sr		bra2	sel4	sr		employee4
6	wed	5	vec				bra4	sel4	mr		employee5
7	thu	6	bra1	sel1	mr		tr				employee6
8	fri	7	tr				bra3	sel2	sr		employee7
summary

Cell Formulas
Range	Formula
L2:W8	L2	=IFNA(COUNTIF(INDEX($C$2:$I$8,1,MATCH($K2,$C$1:$I$1,0)):INDEX($C$2:$I$8,ROWS($C$2:$I$8),MATCH($K2,$C$1:$I$1,0)+2),L$1),"")

 

[Peter_SSs]

should be zero if the cells in columns D ,I togheter and H,I together are empty

Is that correct?

 

[Mussa]

why?
what do you think?

 

[Peter_SSs]

why?
what do you think?

Because you said columns D, I together. I wondered if that was a mistake and it should have been columns D, E together?

 

[Mussa]

Because you said columns D, I together.

sorry about it

it should have been columns D, E together?

yes you're right.

 

[Peter_SSs]

how can I expand the formula to include the days

I wouldn't be expanding that formula since this is a very different question. I would use a separate formula for the days.

Mussa.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	days	date	employee1				employee2					bra1	bra2	bra3	bra4	sel1	sel2	sel3	sel4	mr	sr	vec	tr	sat	sun	mon	tue	wed	thu	fri
2	sat	1	bra1	sel1	mr		bra2	sel1	mr		employee1	4	1	0	0	3	1	1	0	4	1	1	1	1	1	1	1	0	1	0
3	sun	2	bra1	sel1	mr		tr	sel1			employee2	0	2	1	1	2	1	0	2	2	2	1	2	1	1	0	1	1	0	1
4	mon	3	bra1	sel2	mr		vec				employee3
5	tue	4	bra2	sel3	sr		bra2	sel4	sr		employee4
6	wed	5	vec				bra4	sel4	mr		employee5
7	thu	6	bra1	sel1	mr		tr				employee6
8	fri	7	tr				bra3	sel2	sr		employee7
Count per Employee (2)

Cell Formulas
Range	Formula
L2:W8	L2	=IFNA(COUNTIF(INDEX($C$2:$I$8,1,MATCH($K2,$C$1:$I$1,0)):INDEX($C$2:$I$8,ROWS($C$2:$I$8),MATCH($K2,$C$1:$I$1,0)+2),L$1),"")
X2:AD8	X2	=IFNA(--(INDEX($C$2:$I$8,MATCH(X$1,$A$2:$A$8,0),MATCH($K2,$C$1:$I$1,0)+1)&INDEX($C$2:$I$8,MATCH(X$1,$A$2:$A$8,0),MATCH($K2,$C$1:$I$1,0)+2)<>""),"")