ปัญหาการรวมข้อมูลหลายรายการที่ซ้ำกัน บางรายการไม่รวมเป็นค่าเดียว บางรายการไม่มีแต่ยังแสดงผลรวม

[Bo_ry]

Sheet Data เอาวรรคออกจาก Eye95
"Eye95" "Eye95 "

Component Output
B3
=IFERROR(IFNA(INDEX('Project Input'!$C$3:$K$28,SMALL(IF(FREQUENCY(IF(INDEX('Project Input'!$C$3:$K$28,,MATCH(B$2,'Project Input'!$C$2:$K$2,))<>"",MATCH(INDEX('Project Input'!$C$3:$K$28,,MATCH(B$2,'Project Input'!$C$2:$K$2,)),INDEX('Project Input'!$C$3:$K$28,,MATCH(B$2,'Project Input'!$C$2:$K$2,)),)),ROW($A$1:$A$28)),ROW($A$1:$A$28)),ROWS(B$3:B3)),MATCH(B$2,'Project Input'!$C$2:$K$2,)),IF(TRIM(A3)>"0",SUMIFS('Project Input'!$B$3:$B$28,INDEX('Project Input'!$C$3:$K$28,,MATCH(A$2,'Project Input'!$C$2:$K$2,)),A3),"")),"")

copy ไปถึง R3 และ A3

ถ้าใช้ Excel365
Y3
=IFNA(IFNA(UNIQUE(INDEX('Project Input'!$C$3:$K$28,,MATCH(Y$2,'Project Input'!$C$2:$K$2,))),IF(X3#<>"",SUMIFS('Project Input'!$B$3:$B$28,INDEX('Project Input'!$C$3:$K$28,,MATCH(X$2,'Project Input'!$C$2:$K$2,)),X3#),"")),"")

[Bo_ry]

F3
=IF(TRIM(E3)>"0",SUMIFS('Project Input'!$B$3:$B$28,'Project Input'!$D$3:$D$28,E3)+SUMIFS('Project Input'!$B$3:$B$28,'Project Input'!$H$3:$H$28,E3),"")

J3
=IF(TRIM(I3)>"0",SUMIFS('Project Input'!$B$3:$B$28,'Project Input'!$F$3:$F$28,I3)+SUMIFS('Project Input'!$B$3:$B$28,'Project Input'!$K$3:$K$28,I3),"")

K3
=IFERROR(INDEX('Project Input'!$H$3:$H$28,SMALL(IF(FREQUENCY(IF('Project Input'!$H$3:$H$28<>"",IF(ISNA(MATCH('Project Input'!$H$3:$H$28,$E$3:$E$36,)),MATCH('Project Input'!$H$3:$H$28,'Project Input'!$H$3:$H$28,))),ROW($A$1:$A$28)),ROW($A$1:$A$28)),ROWS(K$3:K3))),"")


Q3
=IFERROR(INDEX('Project Input'!$K$3:$K$28,SMALL(IF(FREQUENCY(IF('Project Input'!$K$3:$K$28<>"",IF(ISNA(MATCH('Project Input'!$K$3:$K$28,$I$3:$I$36,)),MATCH('Project Input'!$K$3:$K$28,'Project Input'!$K$3:$K$28,))),ROW($A$1:$A$28)),ROW($A$1:$A$28)),ROWS(K$3:K3))),"")