如何写这个countifs函数

swjia8315

红色区域，如何写countifs函数，统计对应人和时间单元格中，值小于50%的单元格个数？
文件附入

wstwfs

I2=SUMPRODUCT(($A$2:$A$11=$H2)*($B$1:$E$1=I$1)*($B$2:$E$11<0.5))

hxj119

=COUNTIFS(OFFSET($A$1,1,MATCH(I$1,$B$1:$E$1,0),10),"<0.5",$A$2:$A$11,$H2)

zhangcheng6688

学习一下字典，打扰了
Sub test()
arr = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr, 2)
   For j = 2 To UBound(arr)
      If arr(j, i) < 0.5 Then
         d(arr(j, 1) & arr(1, i)) = d(arr(j, 1) & arr(1, i)) + 1
      End If
   Next
Next
brr = [i1].CurrentRegion
For i = 2 To UBound(brr)
   For j = 2 To UBound(brr, 2)
      If d.exists(brr(i, 1) & brr(1, j)) Then
         brr(i, j) = d(brr(i, 1) & brr(1, j))
      End If
   Next
Next
[i1].CurrentRegion = brr
End Sub

ww87725244

=COUNTIFS($A$2:$A$11,$H2,OFFSET($A$1,1,MATCH(I$1,$B$1:$E$1,),COUNTA($A$2:$A$11)),"<0.5")

我的城市你来过

=COUNTIFS($A:$A,$H2,D:D,"<=50%")

1786024734

1. =COUNT(0/($B$2:$E$11<0.5)/($A$2:$A$11=$H3)/($B$1:$E$1=I$1))

复制代码

hcm19522

=COUNTIFS($A$2:$A$11,$H2,OFFSET($A$2,,MATCH(I$1,$1:$1,)-1,10),"<0.5")