# Canonical SUMIFS

Frequent Contributor

# Canonical SUMIFS

Question to our resident Excel boffins.

I am writing a model that relies a lot on SUMIFS.  The layout of the model is very rigorous to provide the necessary scaffolding in a "canonical" fashion, such that sum_range and criteria_range always span the same rows.  The sum_range is always a vertical, eg AA1:AA100, BB120:BB140...  The criteria_range is always in the corresponding rows in col C.

Now, it is extremely tiresome to constantly write (or, as a reader, to read)

=SUMIFS(BB\$120:BB\$140;\$C120:\$C140;criteria)

This becomes even more tiresome when I define BB\$120:BB\$140 as a name "inRange" because when I now write

=SUMIFS(inRange;\$C120:\$C140;criteria)

it is no longer obvious what the corresponding criteria_range should be.  (Defining "inCriteria" is tiresome too, since I contend with too many local names already.)

Happily, we now have Lambdas and I define

mySUMIFS = LAMBDA(sum_range,criteria,SUMIFS(sum_range,SAMEROWS(\$C:\$C,sum_range),criteria))

such that I can simply code

=mySUMIFS(inRange,criteria)

You can of course guess the definition of SAMEROWS but I will include it here for completeness:

``````SAMEROWS = LAMBDA(super_range,sub_range, LET( _
subRow1, ROW(INDEX(sub_range,1,1)), _
subRowN, ROW(INDEX(sub_range, ROWS(sub_range),1)), _
supRow0, ROW(INDEX(super_range,1,1)) - 1, _
INDEX(super_range,subRow1-supRow0,1):INDEX(super_range,subRowN-supRow0,COLUMNS(super_range)) _
))``````

All of this works well enough, except ... there is an issue.

I hoped it would be clear to Excel that my model is only dependent on the values in rows 120:140 in column C.  The crafty coding of SAMEROWS was meant to assure this.  Alas, Excel does not see it that way and I seem to have the problem that alterations of eg cell C50 trigger a recalculation of mySUMIFS.

After this long intro, at last my question:  Is there a better way of finding the canonical criteria_range, given a sum_range, that will allow Excel to recognize the real dependencies?

0 Replies