Using SUMIFS formula with dynamic range-CodePudding

I wonder if there is a way I can use the below SUMIFS formula on a dynamic range without having to click on the formula and drag down to capture the whole range manually every time

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

CodePudding user response：

If the range size is fixed, I suggest that you can use the SUMIFS function and wrapped OFFSET function inside it.

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

Use the $I$58 as the reference in offset function, and replace the sum_range, criteria_range1, etc. in SUMIFS function.

Next time, just change some numbers in the formula, but you need to do some calculators.

Hope it can be helpful.

Note:

=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)

CodePudding user response：

While I think using the entire column might be best option, if you really did want a dynamic range that went from where your specified in the formula, to the bottom row (so I$58:$I????), you could use this:

=SUMIFS($I$58:INDEX($I:$I,MAX(FILTER(ROW(I:I),NOT(ISBLANK($I:$I)))),1),
     $J$58:INDEX($J:$J,MAX(FILTER(ROW(J:J),NOT(ISBLANK($J:$J)))),1),"OK",
       $F$58:INDEX($F:$F,MAX(FILTER(ROW(J:J),NOT(ISBLANK($F:$F)))),1),C3,
       $C$58:INDEX($C:$C,MAX(FILTER(ROW(M:M),NOT(ISBLANK($C:$C)))),1),B3)