Question

在excel中SUMPRODUCT((COUNTIF($G2:$L2,$B2:$D2)>0)*1)

Answer 1

=SUMPRODUCT((COUNTIF($B2:$D2,$G2:$L2)>0)*1)

这样或许对你有帮助

这个最好用VBA写容易些.例如你的公式输入在A9单元格

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

dim i , j as integer

for i=2 to 4

for j=7 to 13

k=0

if cells(2,j)=cells(2,i) then

k=k+1

endif

cells(9,1)=k

End Su