Excel COUNT（）与COUNTIF（）与数组 [英] Excel COUNT() vs COUNTIF() with arrays

问题描述

我认为这应该是一个直截了当的问题，但由于某种原因，我无法找到任何解决方案。

I think this should be a straightforward question, but for some reason I can't find a solution anywhere.

我有一个冗长的公式，返回四个项目的数组 - 即{1,2,0,0}。我想计算出结果数字中有多少大于零。

I have a lengthy formula in excel that, ultimately, returns an array of four items -- i.e. {1,2,0,0}. I want to count how many of the resulting numbers are greater than zero.

当我使用 = COUNT（{1,2,0,0 }）对于此结果，我得到的预期答案为4.但是当我尝试使用 = COUNTIF（{1,2,0,0}，> 0 ）它弹出一个错误，说我的公式有问题。

When I use =COUNT({1,2,0,0}) on this result, I get the expected answer of 4. But when I try to use =COUNTIF({1,2,0,0}, ">0") it pops up with an error saying that my formula has a problem.

有没有什么我做错了？是否有一个等价于COUNTIF（）的数组？

Is there something I'm doing wrong? Is there an array equivalent for COUNTIF() ?

推荐答案

出现 COUNTIF 函数只适用于范围，而 COUNT 函数可以使用数组。

It appears the COUNTIF function only works on ranges, while the COUNT function can utilize an array.

尝试给 SUMPRODUCT 以下是我用来测试公式的例子的稍微扩展的形式。它基本上检查数组中的每个值是否大于0，如果是，则它赋值为1.然后 SUMPRODUCT 遍历并将所有数据相加1，给出总值大于0的值。

Give SUMPRODUCT a try. Below is a slightly expanded form of your example which I used to test the formula. It basically checks to see if each value in the array is greater than 0, and if it is, it assigns it a value of 1. Then SUMPRODUCT goes through and adds up all the 1s to give you the total number of values greater than 0.

=SUMPRODUCT(IF({1,0,3,0,5,0,0,6,9,9,0,7,0}>0,1,0))

这篇关于Excel COUNT（）与COUNTIF（）与数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！