不使用辅助列，条件计数问题

我的城市你来过

=VLOOKUP($B9,$G:$I,{2,3},0)
=COUNTIFS($C:$C,">=1",$D:$D,LEFT($L9,1),$E:$E,$L9)

=SUM(--(SUMIFS($C$8:$C$3439,$B$8:$B$3439,UNIQUE(IF(($D$8:$D$3439=LEFT($L9,1))*($E$8:$E$3439=$L9),$B$8:$B$3439,"")))>=1))

tmplinshi

1. =SUM(--(INDEX(UNIQUE(FILTER(HSTACK($B$9:$B$3439,SUMIF($B$9:$B$3439,$B$9:$B$3439,$C$9:$C$3439)),$E$9:$E$3439=L9)),,2)>=1))

复制代码

如果我在工作中用这个公式，我肯定是会用辅助列，或者用 BYROW 之类的循环，不然计算速度太慢了。

1. =LET(
   
2.     totalSales, SUMIF(B9:B3439, B9:B3439, C9:C3439),
   
3.     BYROW(L9:L34, LAMBDA(row,
   
4.         SUM(--(INDEX(UNIQUE(FILTER(HSTACK(B9:B3439, totalSales), E9:E3439 = row)), , 2) >= 1))
   
5.     ))
   
6. )

复制代码