excel - 计数单元格(符合标准) 与其他单元格的偏移量(符合标准)

Question

我被要求创建一个公式来计算跨不连接范围按月份分类的预测日期数。我还必须根据预测日期(从预测日期行向下 2 行)计算完成的里程碑数量(5 天内)。

我也无法更改数据，或向原始工作表添加任何内容。 Range 将在添加项目时添加行。

仅公式 - 无宏

A            B           C           D
             Milestone    M1          M2          M3
Project X    ORIG. PLAN  5/19/2014  6/23/2014   6/23/2014
             FORE-CAST   7/3/2014   11/21/2014  8/11/2014
             ACTUAL      7/16/2014  12/2/2014   
             MS Delay:   13         11          
             MS Missed:  0          0           0
             MS Score:   100%       100%        20%
Project Y    ORIG. PLAN  5/6/2014   5/6/2014    5/29/2014
             FORE-CAST   5/6/2014   5/6/2014    6/6/2014
             ACTUAL      5/29/2014  5/29/2014   5/29/2014
             MS Delay:   23         23          0
             MS Missed:  0          0           0
             MS Score:   100%       100%        100%

我在想出正确的公式时遇到了一些麻烦，使用 INDEX 和 MATCH 函数甚至计算具有指定范围的日期，更不用说偏移量了。

理想情况下，输出看起来像这样

                            May 2014    Jun 2014    July 2014
# of Milestones             2           1           1
# of Milestones Achieved    0           1           0

Answer 1

SUMPRODUCT 的一些明智使用没有 OFFSET 的交错范围应该能够做到这一点.



公式是:

H1 ► =EDATE(EOMONTH(MIN(INDEX(($C$2:$E$999)+($C$2:$E$999<EDATE(TODAY(), -24))*1E+99, , )), -1)+1, COLUMN(A:A)-1)
H2 ► =SUMPRODUCT(($B$2:$B$999="FORE-CAST")*($C$2:$E$999>=H$1)*($C$2:$E$999<EDATE(H$1,1)))
H3 ► =SUMPRODUCT(($B$5:$B$999="MS Delay:")*($C$5:$E$999>=0)*($C$5:$E$999<5)*($C$3:$E$997>=H$1)*($C$3:$E$997<EDATE(H$1,1))*($C$5:$E$999<>""))

必要时填写正确。我任意使用了两年的窗口来收集可用的最小月份。

请注意 H3 公式中两个交错的行范围。 C3:E997 的行数与 B5:B999 和 C5:E999 相同；只需向上偏移两行。