Canonical SUMIFS

Frequent Contributor

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)




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




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




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