How to use functions instead of range in sumif function excel

Question

I want to use function instead of range in excel SUMIF() function like i used below

=SUMIF(row(E14:P14),"<=in_max_row",E14:P14)

I want the sum of that row only if that row is less than or equal to in_max_row number which has some row number.

I tried the above formula, but it is not working.

Answer 1

SUMIFS doesn't accept ROW(E14:P14) as criteria range!

You should try this LET-formula - it helps you to have multiple steps/calculations within one formula.

Each calculation is assigned to a variable which then can be reused in following calculations.

=LET(maxRow,*put your calculation here*,
SUM(FILTER(E14:P14,ROW(E14:P14)<maxRow)))

SUM plus FILTER allows you to check the row of each cell ...