OFFSET and TRIMRANGE

To learn more about the TRIMRANGE reference check out my previous post – link below.

https://www.excelyourself.com.au/blog/trimrange-references-in-excel

Have a look at the data set in the image below.

We want to be able to perform a flexible SUMIFS calculation based on this dataset expanding. As we add new data to the bottom of the table the calculation should still work. We are not going to apply the format as table feature to this table.

The formula that we have in cell F2 doesn’t work because the use of two TRIMRANGE references returns different ranges between the two columns. This breaks the SUMIFS function which requires the ranges to match. In this case the amount column has fewer entries than the State column.

I think the OFFSET function may be used more with this TRIMRANGE referencing because we can capture one flexible range and then use the OFFSET function to create other ranges that match the flexible range.

The image below has the alternative formula which is flexible and doesn’t return an error.

The formula is.

=LET(rng,B2:.B11,SUMIFS(OFFSET(rng,0,1),rng,E2))

The LET function allows us to capture the flexible range (the State column) in the rng variable. We can then use the OFFSET function to select the matching range one column over using OFFSET(rng,0,1). This ensures the flexible range and the Amount range line up perfectly.

It is early days for this new referencing functionality, but it looks like it will prove very useful.