统计部门总人数

GHH123GHH

杨杨1991315

L3=SUMPRODUCT((MONTH($I$3:$I$9)=$K3)*($C$3:$C$9=L$2))

ww87725244

=COUNTIFS($C$3:$C$9,L$2,$I$3:$I$9,"<="&DATE(2024,$K3,1))

mykitami

=COUNTIFS($I:$I,"<="&--("2024-"&$K3),$C:$C,L$2)

mykitami

=COUNTIFS($I:$I,"<="&--("2024-"&$K3),$C:$C,L$2)

edwin11891

L3：

1. =LET(sBirth,F3:F9,sDept,C3:C9,sName,B3:B9,sMonth,K3:K14,t,DROP(PIVOTBY(sBirth,HSTACK(MATCH(sDept,sDept,),sDept),sName,COUNTA,0,0,,0),2),s,REDUCE("",sMonth,LAMBDA(x,y,VSTACK(x,IFERROR(FILTER(DROP(t,,1),TAKE(t,,1)=y),"")))),DROP(IFERROR(s,""),1))

复制代码

如果需要列举出是哪些人的生日，公式为：

1. =LET(sBirth,F3:F9,sDept,C3:C9,sName,B3:B9,sMonth,K3:K14,t,DROP(PIVOTBY(sBirth,HSTACK(MATCH(sDept,sDept,),sDept),sName,LAMBDA(x,TEXTJOIN(",",,x)),0,0,,0),2),s,REDUCE("",sMonth,LAMBDA(x,y,VSTACK(x,IFERROR(FILTER(DROP(t,,1),TAKE(t,,1)=y),"")))),DROP(IFERROR(s,""),1))

复制代码

蚊香馅包子

=SUM(($I$3:$I$9<DATE(2024,SUBSTITUTE($K3,"月","")+1,1))*($C$3:$C$9=L$2))
区域数组 Ctrl+Shift+Enter

小小白君

1. =SUM(($I$3:$I$9<45323)*($C$3:$C$9=L$2)*(MONTH($I$3:$I$9)=$K3))

复制代码

小小白君

=SUM(($I$3:$I$9<45323)*($C$3:$C$9=L$2)*(MONTH($I$3:$I$9)=$K3))

zhangcheng6688

学习一下字典
Sub test()
arr = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 3 To UBound(arr)
   If arr(i, 9) <= #1/1/2024# Then
      Set d(arr(i, 3)) = CreateObject("scripting.dictionary")
   End If
   If d.exists(arr(i, 3)) Then
      yf = Month(arr(i, 9)) & "月"
      d(arr(i, 3))(yf) = d(arr(i, 3))(yf) + 1
   End If
Next
brr = [k2].CurrentRegion
For i = 2 To UBound(brr)
   For j = 2 To UBound(brr, 2)
      If d.exists(brr(1, j)) Then
         If d(brr(1, j)).exists(brr(i, 1) & "月") Then
            brr(i, j) = d(brr(1, j))(brr(i, 1) & "月")
         End If
      End If
   Next
Next
[k2].CurrentRegion = brr
End Sub