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

 

[Mussa]

BTW, it does require your Excel 2019 not Excel 2010

absolutely yes !

Microsoft Excel .xlsx
	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	5	1	0	0	4	1	1	0	5	1	1	1	#VALUE!	0	0	0	0	0	0
3	sun	2	bra1	sel1	mr		tr	sel1			employee2	0	2	1	1	3	1	0	2	2	2	1	3	0	#VALUE!	0	0	0	0	0
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	sat	1	bra1	sel1	mr
10	sun	2					tr	sel1
11
1

Cell Formulas
Range	Formula
L2:W8	L2	=IFNA(COUNTIF(INDEX($C$2:$I$10,1,MATCH($K2,$C$1:$I$1,0)):INDEX($C$2:$I$10,ROWS($C$2:$I$10),MATCH($K2,$C$1:$I$1,0)+2),L$1),"")
X2:AD3	X2	=IF(ISNUMBER(MATCH($K2,$C$1:$G$1,0)),(LEN(SUBSTITUTE(TEXTJOIN("|",0,IF($A$2:$A$10=X$1,IF($C$1:$G$1=$K2,$A$2:$A$10&$D$2:$H$10&$E$2:$I$10,""),"")),X$1&"||",""))-LEN(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("|",0,IF($A$2:$A$10=X$1,IF($C$1:$G$1=$K2,$A$2:$A$10&$D$2:$H$10&$E$2:$I$10,""),"")),X$1&"||",""),X$1,"")))/3,"")
X4:AD8	X4	=IFNA(--(INDEX($C$2:$I$10,MATCH(X$1,$A$2:$A$10,0),MATCH($K4,$C$1:$I$1,0)+1)&INDEX($C$2:$I$10,MATCH(X$1,$A$2:$A$10,0),MATCH($K4,$C$1:$I$1,0)+2)<>""),"")

 

[Peter_SSs]

The formula works for me with that sample data. However, the formula refers to rows 2:10 and you have only shown down to row 8. Could there be any #VALUE! error values in rows 9 or 10?

 

[Mussa]

you see to my picture before last updated

Could there be any #VALUE! error values in rows 9 or 10?

actually I check it , I don't fine any thing . so I decide attach the file maybe find out somthing
cont.xlsx

 

[Peter_SSs]

When I open your file, the formulas in the 'day' columns include lots of "@" symbols. eg For X2

=IF(ISNUMBER(MATCH($K2,$C$1:$G$1,0)),(LEN(SUBSTITUTE(TEXTJOIN("|",0,IF(@$A$2:$A$10=X$1,IF(@$C$1:$G$1=$K2,@$A$2:$A$10&@$D$2:$H$10&@$E$2:$I$10,""),"")),X$1&"||",""))-LEN(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("|",0,IF(@$A$2:$A$10=X$1,IF(@$C$1:$G$1=$K2,@$A$2:$A$10&@$D$2:$H$10&@$E$2:$I$10,""),"")),X$1&"||",""),X$1,"")))/3,"")

Remove them and see how you go.

 

[Mussa]

the formulas in the 'day' columns include lots of "@" symbols.

but why doesn't show me
it doesn't accept the formula and there is a problem in this part IF(@$A$2:$A$10=X$1 and the cursor stay focus @$A$2 .

 

[Peter_SSs]

Try removing them and confirm the formula with Ctrl+Shift+Enter, not just Enter.

 

[Mussa]

truly I no know how show symbol @ for you but not for me !!!

confirm the formula with Ctrl+Shift+Enter

any way your suggestion works perfectly .
much appreciated for your effort & time

 

[Peter_SSs]

truly I no know how show symbol @ for you but not for me !!!

It relates to our different Excel versions, what version a formula was created in and what version the formula is being viewed in.
See here for more info: Implicit intersection operator: @

any way your suggestion works perfectly .
much appreciated for your effort & time

You're welcome. Glad it is resolved.