函数去重并筛选

ychenu111

各位好，有个问题想请假，希望用函数得出每人激活的客户数量有多少？里面有客户号是重复的，需去重筛选，谢谢！

静看流年RSSQ

先上一个标准的MATCH  去重套路。 数组公式  

=SUM(N(IFERROR(MATCH(F2&"激活"&$C$2:$C$16,$B$2:$B$16&$D$2:$D$16&$C$2:$C$16,)=ROW($1:$15),)))

就你附件模拟的数据 。一个客户只对应了2种状态。相当于也是没有重复的、
=SUM(N(F2&"激活"&$C$2:$C$16=$B$2:$B$16&$D$2:$D$16&$C$2:$C$16))

最后上矿泉水      
=COUNTA(UNIQUE(FILTER($C$2:$C$16,($B$2:$B$16=F2)*($D$2:$D$16=$D$2))))

静看流年RSSQ

SUMPRODUCT  这个去重写不来。。乱凑的。。。   模拟数据是对的。   有事情。撤退、

=SUMPRODUCT(IFERROR((($B$2:$B$16=F2)*($D$2:$D$16=$D$2))/COUNTIFS($C$2:$C$16,$C$2:$C$16,$D$2:$D$16,$D$2,$B$2:$B$16,F2),))
应该是这样的写法吧 。不会也懒的验证。反正我肯定是用矿泉水的。
=SUMPRODUCT((($B$2:$B$16=F2)*($D$2:$D$16=$D$2))/COUNTIFS($C$2:$C$16,$C$2:$C$16,$D$2:$D$16,$D$2:$D$16,$B$2:$B$16,$B$2:$B$16))

oldshuijing

=SUMPRODUCT(N(MATCH(IF((F2=$B$2:$B$16)*($D$2:$D$16="激活"),$C$2:$C$16,""),IF((F2=$B$2:$B$16)*($D$2:$D$16="激活"),$C$2:$C$16,""),0)=ROW($1:$15)))-1

ychenu111

感谢各位相助，我学习一下，谢谢

solextrade

静老师还在坚守岗位呀，可敬！我也来一个，然后出去送礼了：
数组公式：
=COUNT(0/FREQUENCY(IF((B$2:B$17=F2)*(D$2:D$17="激活"),C$2:C$17),(B$2:B$17=F2)*C$2:C$17))

liyanghu

水一个数组
=SUM(N((B$2:B$16=F2)*MATCH(C$2:C$16,C$2:C$16,)*(D$2:D$16=LEFT(G$1,2))>0))