使用具有动态范围的SUMIFS公式

Question

我想知道是否有一种方法可以在动态范围内使用下面的SUMIFS公式，而无需每次时点击公式并向下拖动即可手动捕获整个范围

=SUMIFS($I$58:$I$573,$J$58:$J$573,"OK",$F$58:$F$573,C3,$C$58:$C$573,B3)

Answer 1

如果范围大小是固定的，我建议您可以使用SUMIFS函数并在其中封装OFFSET功能。

OFFSET(reference, rows, cols, [height], [width])

在offset函数中使用$I$58作为引用，并替换SUMIFS函数中的sum_range、criteria_range1等。

下次，只要改变公式中的一些数字，但你需要做一些计算器。

希望能有所帮助。

注：

=SUMIF(A2:A10,A2,B2:B10)
=SUMIF(OFFSET(A2,0,0,9,1),A2,OFFSET(B2,0,0,9,1))
=SUMIFS(I58:I573,J58:J573,"OK",F58:F573,C3,C58:C573,B3)
=SUMIFS(OFFSET(I58,0,0,516,1),OFFSET(J58,0,0,516,1),"OK",OFFSET(F58,0,0,516,1),C3,OFFSET(C58,0,0,516,1),B3)

Answer 2

已更新

我注意到，由于重复的最后一行计算，我之前的公式(现已删除(的执行速度比volatile函数(？？(慢。简单的解决方案是让一个单元格明确地说明最后一行，或者只使用LET函数。

=LET(lastRow,MAX(FILTER(ROW(J:J),J:J<>"")),
SUMIFS( $I$58:INDEX($I:$I,lastRow,1),
$J$58:INDEX($J:$J,lastRow,1),"OK",
$F$58:INDEX($F:$F,lastRow,1),C3,
$C$58:INDEX($C:$C,lastRow,1),B3))

我还注意到，被接受的答案只是对最后一行进行了硬编码(在我看来，这似乎违背了这个问题的全部目的，但我跑题了(。这个函数做完全相同的事情，但不是一个易失函数。坦率地说，这似乎比使用常规范围更有效，但再次。。。我离题了。。。

=SUMIFS($I$58:INDEX($I:$I,573),1),$J$58:INDEX($J:$J,573,1),"OK",
$F$58:INDEX($F:$F,573,1),C3,$C$58:INDEX($C:$C,573,1),B3)