COUNTIF(如果值不在一行中) [英] COUNTIF if values are not in one row
问题描述
Excel
A B C D E F
1 4 400
2 0
3 700
4 0
5 300
6 0
7 0
8 100
9
在上面的 Excel
表中,我在不同的单元格中都有值.
在单元格 A1
中,我想计算包含值<>的单元格的数量.0
.
因此,我尝试使用以下公式:
In the above Excel
table I have values in different cells.
In cell A1
I want to count the number of cells that contain a value <> 0
.
Therefore, I tried to go with this formula:
A1 = COUNTIF((D1,E2,F3,D4,D5,F6,F7,E8),0)
但是,通过此公式,我得到 #VALUE!
.
我认为问题是 COUNTIF
公式的 array
不在一行中.
However, with this formula I get #VALUE!
.
I assume the issue is that the array
for the COUNTIF
formula is not in one row.
解决此问题的一种方法是创建一个帮助程序列,其中将所有值都放在一行中,但我想知道是否有另一种方法来实现此目的而没有任何帮助程序列?
One way of solving the issue would be to make a helper column in which I put all the values in one row but I am wondering if there is another way of doing it without any helper column?
推荐答案
如果必须使用非连续范围,则可以使用:
If using a non-continuous range is a must you could use:
=SUM(COUNTIF(INDIRECT({"D1","E2","F3","D4:D5","F6:F7","E8"}),"<>0"))
请注意,这是易变的!
这篇关于COUNTIF(如果值不在一行中)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!