Question

excel统计某个区间的内某个值得个数

如图，怎么统计1-50中有几个A、S、D,51-100中有几个这些，101-150有几个？
ps：怎么输入1-50，51-100，这样的数据，快速填充填充的是1-51

Answer 1

统计1-50中有几个A、S、D
公式为
=SUM(COUNTIFS(A:A,">=1",A:A,"<=50",B:B,{"A","S","D"}))

51-100中有几个A、S、D
公式为
=SUM(COUNTIFS(A:A,">=51",A:A,"<=100",B:B,{"A","S","D"}))

101-150有几个A、S、D
公式为
=SUM(COUNTIFS(A:A,">=101",A:A,"<=150",B:B,{"A","S","D"}))

如果只想用一个公式来下拉,则公式为
=SUM(COUNTIFS(A:A,">="&ROW(A1)*50-49,A:A,"<="&ROW(A1)*50,B:B,{"A","S","D"}))

如果不是一次的合计A、S、D,而是分开统计的,则
求A个数的公式为
=COUNTIFS(A:A,">="&ROW(A1)*50-49,A:A,"<="&ROW(A1)*50,B:B,"A")

求S个数的公式为
=COUNTIFS(A:A,">="&ROW(A1)*50-49,A:A,"<="&ROW(A1)*50,B:B,"S")

求D个数的公式为
=COUNTIFS(A:A,">="&ROW(A1)*50-49,A:A,"<="&ROW(A1)*50,B:B,"D")

分别下拉填充

追问

是分别统计。。几个A几个s几个D

追答

如果不是一次的合计A、S、D,而是分开统计的,则
求A个数的公式为
=COUNTIFS(A:A,">="&ROW(A1)*50-49,A:A,"="&ROW(A1)*50-49,A:A,"="&ROW(A1)*50-49,A:A,"<="&ROW(A1)*50,B:B,"D")

分别下拉填充

怎么输入1-50，51-100，这样的数据，快速填充填充的是1-51
单元格写入公式
=ROW(A1)*50-49&"-"&ROW(A1)*50
下拉填充公式

Answer 2

统计1-50中有几个A、S、D
公式为
=SUM(COUNTIFS(A:A,">=1",A:A,"<=50",B:B,{"A","S","D"}))
51-100中有几个A、S、D
公式为
=SUM(COUNTIFS(A:A,">=51",A:A,"<=100",B:B,{"A","S","D"}))
101-150有几个A、S、D
公式为
=SUM(COUNTIFS(A:A,">=101",A:A,"<=150",B:B,{"A","S","D"}))
如果只想用一个公式来下拉,则公式为
=SUM(COUNTIFS(A:A,">="&ROW(A1)*50-49,A:A,"<="&ROW(A1)*50,B:B,{"A","S","D"}))
如果不是一次的合计A、S、D,而是分开统计的,则
求A个数的公式为
=COUNTIFS(A:A,">="&ROW(A1)*50-49,A:A,"<="&ROW(A1)*50,B:B,"A")
求S个数的公式为
=COUNTIFS(A:A,">="&ROW(A1)*50-49,A:A,"<="&ROW(A1)*50,B:B,"S")
求D个数的公式为
=COUNTIFS(A:A,">="&ROW(A1)*50-49,A:A,"<="&ROW(A1)*50,B:B,"D")
分别下拉填充

Answer 3

统计1-50中有几个A、S、D,
=SUM(COUNTIFS(A:A,">=1",A:A,"<=50",B:B,{"A","S","D"}))
或
=SUMPRODUCT((A1:A999>=1)*(A1:A999<=50)*((B1:B999="A")+(B1:B999="S")+(B1:B999="D")))
其他问题的公式可依此类推