按月计算的计数日期(不包括连字符)

Question

我在Excel工作表(工作表名称: Status)中分配给我的任务数量在AC列中输入关闭日期。我想根据关闭的月份和年份来计算已关闭的任务。但是我有许多任务没有关闭，我用连字符(-)输入相应的单元格(AC列)。我使用下面的公式来计算已关闭的任务数。

SUMPRODUCT((MONTH(Status!$AC$1:$AC$100)=MONTH(DATEVALUE(G21&" 1")))*(YEAR(Status!$AC$1:$ACA$100)=2020))

如果以日期输入从AC1到AC100的单元格，则公式有效。但是，当任务没有关闭时，我在一些单元格中输入连字符(-)( AC1到AC100之间)，公式返回错误#值！

能否帮助您根据月份和年份在a 1:a 100范围内计算关闭的任务，其中不包括单元格中带有公式的连字符(H21)。我有一个月名(格式: MMM)在另一个工作表从G21开始。在H21中输入公式。

Answer 1

发生#VALUE错误是因为MONTH和YEAR函数期望参数为串行日期。通过使用- (即文本)，MONTH和YEAR函数返回一个#VALUE错误，这将导致公式的其余部分返回#VALUE。

对于打开的任务，您可以使用一个空白单元格而不是"-“，您的公式将有效。如果必须使用"-"，那么首先使用数组IF修改"-“

=SUMPRODUCT((IF($AC$1:$AC$100="-",0,MONTH($AC$1:$AC$100))=MONTH(DATEVALUE(G21&" 1")))*(IF($AC$1:$AC$100="-",0,YEAR($AC$1:$AC$100))=2020))

编辑我添加了下面公式的图像：

​