Snasui Excel Forum

สวัสดีค่ะ แนบรูปกับไฟล์มาด้วยค่ะ ต้องการสูตรในพื้นที่สีเหลืองค่ะ (1rowคือ1วันนะคะ)
column C อยากนับว่ามีรายชื่อที่ต้องมางานทั้งวันกี่คน (มีรายชื่อในรอบเช้าและบ่าย)
column D นับว่ามีคนที่มาครึ่งวันกี่คน (มาเช้าหรือบ่ายเพียงอย่างเดียว)
รายชื่อในcellเดียวกันจะไม่ซ้ำกันอยู่แล้วค่ะ และทุกรายชื่อจะถูกคั่นด้วยเครื่องหมาย ";" ค่ะ

ขอบพระคุณค่ะ

C2
=SUMPRODUCT((TRIM(MID(SUBSTITUTE(A2,";",REPT(" ",99)),ROW(A$1:INDEX(A:A,LEN(A2)-LEN(SUBSTITUTE(A2,";",))+1))*99-98,99))=TRIM(MID(SUBSTITUTE(B2,";",REPT(" ",99)),COLUMN($A$1:$Z$1)*99-98,99)))*(A2<>""))

D2
=SUMPRODUCT(--(MATCH(TRIM(MID(SUBSTITUTE(A2&";"&B2,";",REPT(" ",99)),ROW(A$1:A$99)*99-98,99)),TRIM(MID(SUBSTITUTE(A2&";"&B2,";",REPT(" ",99)),ROW(A$1:A$99)*99-98,99)),)=ROW($A$1:$A$99)))-1-C2

ขอบพระคุณมากค่ะ

อีกวิธีครับ

1. ที่ C2 คีย์
   =SUMPRODUCT(--(COUNTIF(A2,"*"&FILTERXML("<t><s>"&SUBSTITUTE(B2,";","</s><s>")&"</s></t>","//s")&"*")=1))
   Enter > Copy ลงด้านล่าง
2. ที่ D2 คีย์
   =SUMPRODUCT(--(FREQUENCY(MATCH(FILTERXML("<t><s>"&SUBSTITUTE(A2&";"&B2,";","</s><s>")&"</s></t>","//s"),FILTERXML("<t><s>"&SUBSTITUTE(A2&";"&B2,";","</s><s>")&"</s></t>","//s"),0),ROW($1:$10))=1))
   Enter > Copy ลงด้านล่าง

ทั้งวันอีกแบบ 2013+

=COUNT(MATCH(FILTERXML("<n><m>"&SUBSTITUTE(A2,";","</m><m>")&"</m></n>","//m"),FILTERXML("<n><m>"&SUBSTITUTE(B2,";","</m><m>")&"</m></n>","//m"),))