Search code examples
excelsumifsexcel-365

How to use functions instead of range in sumif function excel

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.

Solution

  • 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 ...