可以在范围的值上使用COUNTIF功能吗？ [英] Possible to use COUNTIF with a function on the range's values?
问题描述
假设我有一个从A1开始的日期列表。
1/3/2014
2/5/2014
5/5/2015
8/10/2016
...
我想计算一个月在这个范围内出现的次数。我目前的解决方案是它下面的行只包含 = MONTH（x1），其中 x 是列，那我就调用该行的COUNTIF。
我不认为这是一个很好的解决方案，但它需要一大堆额外的单元格来计算我的电子表格中的月份，这不是真的所以基本上，有没有办法按照= COUNTIF（MONTH（range），5）的方式做一些事情，例如， 5月份发生什么事情？
不，你不能这样做， COUNTIF 函数需要一个范围作为第一个参数 - 范围上的任何操作（如使用 MONTH 函数）将该范围转换为 COUNTIF 不接受
可能的替代方法是使用 SUMPRODUCT eg
= SUMPRODUCT（（MONTH（range）= 5）+0）
或 COUNTIFS 像这样
= COUNTIFS（range， > =& Z1，range，& EOMONTH（Z1,0）+1）
其中Z1是第一他的月数，例如2013年5月1日
当然，SUMPRODUCT版本没有考虑到年度（尽管可以添加），而COUNTIFS则
说明
在 SUMPRODUCT 中使用一个表达式，如 MONTH（range）= 5 ，返回一个 TRUE / FALSE 的数组，如 {TRUE; FALSE; FALSE; TRUE} ....但 SUMPRODUCT 这里只需要数字，所以我们需要一种方法来 -erceTRUE为1，FALSE为0.您可以使用不更改值的任何数学运算来执行此操作，例如+0，* 1或者你可以这样添加到前面：
= SUMPRODUCT（ - （MONTH（range）= 5 ））
所以我们得到一些类似
= SUMPRODUCT（ - （{TRUE; FALSE; FALSE; TRUE}））
...而成为
= SUMPRODUCT（{1; 0; 0; 1}）
然后SUMPRODUCT将这些值相加得到2，即5月份的日期数。
SUMPRODUCT优于 SUM 纯粹是因为您不需要使用
请参阅这里为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屋！
