请教各位大神帮忙解答，如何按销量层级统计客户数

浅蓝烟花

如标题和附件，谢谢。

良良

B502=COUNT(0/FREQUENCY(ROW(A:A),MATCH(A$2:A$501,A$2:A$501,)*(B$2:B$501>=LEFT(A502,IFERROR(FIND("-",A502)-1,5))/1)*(B$2:B$501<=IFERROR(MID(A502,FIND("-",A502)+1,5)/1,9^9))))-1，下拉。

啸海

b502
=INDEX(FREQUENCY(B$2:B$501,LEFT(A$502:A$504,FIND("-",SUBSTITUTE(A$502:A$504,"及以上","-"))-1)-1),ROW(A1))

浅蓝烟花

良良 发表于 2024-12-19 16:55
B502=COUNT(0/FREQUENCY(ROW(A:A),MATCH(A$2:A$501,A$2:A$501,)*(B$2:B$501>=LEFT(A502,IFERROR(FIND("-",A ...

我用的2003版EXCEL，结果变成-1。

浅蓝烟花

啸海 发表于 2024-12-19 16:58
b502
=INDEX(FREQUENCY(B$2:B$501,LEFT(A$502:A$504,FIND("-",SUBSTITUTE(A$502:A$504,"及以上","-"))-1)- ...

谢谢你，可以用。麻烦你了。

solextrade

=INDEX(FREQUENCY(IF(MATCH(A$2:A$501,A:A,)=ROW($2:$501),SUMIF(A$2:A$501,A$2:A$501,B$2)+1%,),{1,3000,6000,12000}),6-ROW(A1))

浅蓝烟花

啸海 发表于 2024-12-19 16:58
b502
=INDEX(FREQUENCY(B$2:B$501,LEFT(A$502:A$504,FIND("-",SUBSTITUTE(A$502:A$504,"及以上","-"))-1)- ...

但是结果不对，大于等于12000的客户只有39家。麻烦帮忙讲解一下。

良良

浅蓝烟花 发表于 2024-12-19 17:01
我用的2003版EXCEL，结果变成-1。

=COUNT(0/FREQUENCY(ROW(A:A),MATCH(A$2:A$501,A$2:A$501,)*(B$2:B$501>=LEFT(A502,FIND("-",SUBSTITUTE(A502,"及以上","-"))-1)/1)*(B$2:B$501<=SUBSTITUTE(MID(A502,FIND("-",SUBSTITUTE(A502,"及以上","-"))+1,5),"以上",9^9)/1)))-1

A九月

浅蓝烟花

良良 发表于 2024-12-19 17:17
=COUNT(0/FREQUENCY(ROW(A:A),MATCH(A$2:A$501,A$2:A$501,)*(B$2:B$501>=LEFT(A502,FIND("-",SUBSTITUTE( ...

谢谢，达到12000的是39，不是38。