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]

this is a very different question

my apologies ! and thanks very much for your time and help

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Mussa]

@Peter_SSs sorry I come back again . I've found if I repeat days for the same employee it doesn't count repeted day .
for instance if I repeat SAT twice for employee 1 it shows 1 . should be 2 how can fix it,please?

 

[Peter_SSs]

Could we have some sample data and expected results that show the new requirements to help with understanding?

 

[Mussa]

1 Microsoft Excel NN.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	6	1	0	0	5	1	1	0	6	1	1	1	1	1	1	1	0	1	0
3	sun	2	bra1	sel1	mr		tr	sel1			employee2	0	3	1	1	4	1	0	2	3	2	1	3	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
9	sat	1	bra1	sel1	mr		bra2	sel1	mr
10	sun	2	bra1	sel1	mr		tr	sel1
ورقة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:AD8	X2	=IFNA(--(INDEX($C$2:$I$10,MATCH(X$1,$A$2:$A$10,0),MATCH($K2,$C$1:$I$1,0)+1)&INDEX($C$2:$I$10,MATCH(X$1,$A$2:$A$10,0),MATCH($K2,$C$1:$I$1,0)+2)<>""),"")

the employee1 contains SAT twice as in A2,9 then should be in X2=2 not 1
also employee 2 contains sun twice as in A3,10 then should be in Y3=2 not 1

 

[Peter_SSs]

What value here for employee1 for sat and please explain the result?

Mussa.xlsm
	A	B	C	D	E
1	days	date	employee1
2	sat	1	bra1
3	sun	2	bra1	sel1	mr
4	mon	3	bra1	sel2	mr
5	tue	4	bra2	sel3	sr
6	wed	5	vec
7	thu	6	bra1	sel1	mr
8	fri	7	tr
9	sat	1	bra1	sel1
10	sun	2	bra1	sel1	mr
11	mon	3
12	tue	4
13	wed	5
14	thu	6
15	fri	7
16	sat	1	bra4	sel1	mr
Sheet2 (2)

 

[Mussa]

based on column A and columns (D,E) together should be 2 . it ignores in row2 because columns D,E are empty .
the same thing about employee2 should ignore if the columns H,I are empty

 

[Peter_SSs]

OK, see if this works for you.

Mussa.xlsm
	A	B	C	D	E	F	G	H	I	J	K													X	Y	Z	AA	AB	AC	AD
1	days	date	employee1				employee2																	sat	sun	mon	tue	wed	thu	fri
2	sat	1	bra1	sel1	mr		bra2	sel1	mr		employee1													2	1	1	1	0	0	1
3	sun	2	bra1	sel1	mr		tr	sel1			employee2													1	2	0	1	1	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									employee6
8	fri	7	abc	x							employee7
9	sat	8	bra1	sel1	mr		tr
10	sun	9	tr				bra3	sel2	sr
Count per Employee (3)

Cell Formulas
Range	Formula
X2:AD8	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,"")

 

[Mussa]

it gives error #VALUE!

 

[Peter_SSs]

it gives error #VALUE!

As you can see, it did not for me.

Perhaps you could post a small XL2BB sample that does produce an error?

BTW, it does require your Excel 2019 not Excel 2010