可以将 COUNTIF 与范围值的函数一起使用吗? [英] Possible to use COUNTIF with a function on the range's values?
问题描述
假设我有一个日期列表，从 A1 开始并跨越...
1/3/20142/5/20145/5/20158/10/2016...
我想计算某个月份出现在此范围内的次数.我当前的解决方案是它下面的行只包含 =MONTH(x1)，其中 x 是列，然后我在该行上调用 COUNTIF.
我不认为这是一个糟糕的解决方案，但它确实需要一大堆额外的单元格来计算我的电子表格中的月份，这对于其他任何事情都不是必需的.
所以基本上，有没有什么方法可以按照 =COUNTIF(MONTH(range),5) 的方式来计算，例如，某事在 5 月份发生的次数?
不，你不能那样做，COUNTIF 函数需要一个范围作为第一个参数 - 对范围的任何操作(比如使用MONTH 函数)将该范围转换为 COUNTIF 不接受的数组
可能的替代方法是使用 SUMPRODUCT 例如
=SUMPRODUCT((MONTH(range)=5)+0)
或 COUNTIFS 像这样
=COUNTIFS(range,">="&Z1,range,"<"&EOMONTH(Z1,0)+1)
其中 Z1 是要计算的月份的第 1 天，例如2013 年 5 月 1 日
当然，SUMPRODUCT 版本不考虑年份(尽管您可以添加)，而 COUNTIFS 则考虑
说明
在 SUMPRODUCT 中，当您使用像 MONTH(range)=5 这样的表达式返回一个 TRUE/FALSE 值的数组"时，例如{TRUE;FALSE;FALSE;TRUE}....但是 SUMPRODUCT 这里只对数字求和，所以我们需要一种方法来强制"TRUE 为 1 和 FALSE 为0. 你可以用任何不改变值的数学运算来做到这一点，例如+0, *1 或者你可以像这样在前面添加 --:
=SUMPRODUCT(--(MONTH(range)=5))
所以我们得到类似的东西
=SUMPRODUCT(--({TRUE;FALSE;FALSE;TRUE}))
...然后变成
=SUMPRODUCT({1;0;0;1})
然后 SUMPRODUCT 将这些值相加得到 2，即 5 月的日期数.
SUMPRODUCT 优于 SUM 纯粹是因为您不需要使用 CTRL+SHIFT+输入
请参阅此处，了解 SUMPRODUCT 及其多种用途>
Let's say that I have a list of dates starting from A1 and going across...
1/3/2014
2/5/2014
5/5/2015
8/10/2016
...
I'd like to count the number of times a certain month appears in this range. My current solution is that the row below it just contains =MONTH(x1), where x is the column, and then I call a COUNTIF on that row.
I don't think that's so bad of a solution, but it does require a whole bunch of extra cells just to calculate months in my spreadsheet, which isn't really necessary for anything else.
So basically, is there any way to do something along the lines of =COUNTIF(MONTH(range),5) to count, for example, the number of times something occurs in May?
No, you can't do that, COUNTIF function requires a range as first argument - any operation on a range (like using MONTH function) converts that range to an array that COUNTIF doesn't accept
Possible alternative are to use SUMPRODUCT e.g.
=SUMPRODUCT((MONTH(range)=5)+0)
or COUNTIFS like this
=COUNTIFS(range,">="&Z1,range,"<"&EOMONTH(Z1,0)+1)
where Z1 is 1st of the month to count, e.g. 1-May-2013
Of course the SUMPRODUCT version doesn't take account of the year (although you could add that in) while COUNTIFS does
Explanation
In SUMPRODUCT when you use an expression like MONTH(range)=5 that returns an "array" of TRUE/FALSE values like {TRUE;FALSE;FALSE;TRUE}....but SUMPRODUCT here only sums numbers so we need a way to "co-erce" TRUE to 1 and FALSE to 0. You can do that with any mathematical operation that doesn't change the value, e.g. +0, *1 or you can add -- to the front like this:
=SUMPRODUCT(--(MONTH(range)=5))
so we get something like
=SUMPRODUCT(--({TRUE;FALSE;FALSE;TRUE}))
...and that becomes
=SUMPRODUCT({1;0;0;1})
and then SUMPRODUCT sums those values to get 2, i.e. the number of dates in May.
SUMPRODUCT is preferred to SUM purely because you don't need to "array enter" the formula with CTRL+SHIFT+ENTER
See here for a good explanation of SUMPRODUCT and it's many uses
这篇关于可以将 COUNTIF 与范围值的函数一起使用吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
