Question

EXCEL中COUNTIF函数怎么计算大于且小于特定值的个数

如图我需要计算出大于-100且小于0.1的值的个数，用公式COUNTIF(W3:W1979,">-100""<0.1")，得到的数为0

Answer 1

1、首先在excel表格中输入一组数据，该组数据有负值有小数数值，需要在该组数据中计算大于-100并且小于0.1的个数。

2、在B1单元格中输入计算公式：=COUNTIFS(A1:A6,">-100",A1:A6,"<0.1")，需要在两个条件的前方都输入区域范围。

3、点击回车即可看到在A1:A6的单元格范围中满足条件的个数有5个。

4、如果修改了取值范围内的数值，即可在B1单元格中看到个数会随着更改自动变化。

Answer 2

版本COUNTIFS可用的话，用：=COUNTIFS(W3:W1979,">-100",W3:W1979,"<0.1")
否则用：=COUNTIF(W3:W1979,">-100")-COUNTIF(W3:W1979,">=0.1")

Answer 3

改用countifs函数
=COUNTIFS(W3:W1979,">-100",W3:W1979,"<0.1")

Answer 4

2007以上版本用countifs
=countifs(W:W,">-100",W:W,"<1")

Answer 5

=SUMPRODUCT((W5:W11>-100)*(W5:W11<0.1))