如何在Excel公式的Countif中使用Year函数? [英] How to use year function inside the Countif in excel formula?

问题描述

我现在无法制作一个excel公式，该公式首先通过从日期中获取年份来搜索值，然后从工作表中搜索另一个值.最后返回计数.

I'm having an trouble making an excel formula at the moment which would first search for the value by getting the year from the dates and then search another value from the sheet. Finally return the count.

请让我知道我该怎么做.我已经被困了很长时间了.

Please, let me know how can I do this. I've been stuck at this a long time.

现在我正在使用此公式.

Right now i'm using this formula.

=COUNTIFS('Inc'!$F$2:$F$984,"YEAR('Incg'!$F$2:$F$984)=2017",'Incident Log'!$J$2:$J$984,$F9)`

推荐答案

在这种情况下，您不能使用 YEAR()作为 COUNTIFS 的参数输出是数值2017，而您要用于比较的值是42736，这是今年的内部表示形式.可能的解决方法是:

You can't use YEAR() as an argument to COUNTIFS in this case, because its output is the numerical value 2017 whereas the value you want to use for your comparison is 42736, the internal representation of this year. A possible workaround is:

=COUNTIFS('Incident Log'!$F$2:$F$984,">="&DATE(2017,1,1),'Incident Log'!$F$2:$F$984,"<"&DATE(2018,1,1), ...)

这将提取2017年1月1日和2018年1月1日的数字日期值，并检查每个值是否位于这些日期之间.

This extracts the numerical date values of January 1 2017 and 2018, and checks whether each value lies between those dates.

如果您确实想使用 YEAR()，则可以将其与 SUMPRODUCT 一起使用，这是在 COUNTIFS <之前解决此类问题的常用方法/code>可用.

If you do want to use YEAR() then you can use it with SUMPRODUCT, which was the usual way of solving problems like this before COUNTIFS became available.

=SUMPRODUCT(--(YEAR('Incident Log'!$F$2:$F$984)=2017),--('Incident Log'!$J$2:$J$984=$F9))

这篇关于如何在Excel公式的Countif中使用Year函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！