Sorting and conserving formula

Michaël @ NDA Group

When I sort rows, the formulas are not maintained:

= SUMIF($[column_A]$7:$[column_A]237, "Received", [Column_B]$7:[Column_B]237))

gets transformed into this:

= SUMIF($[column_A]$57:$[column_A]237, "Received", [Column_B]$57:[Column_B]237))



the $ has no effect.

and I cannot use the [column_A]:[column_A] syntax as it will return me circular error due to my calculation above rows 7.

 

I am looking for the possibility to SUMIF everything under row 7 regardless the sorting.

 

Any suggestion?

Best,

Michaël

L_123

Try doing your sorting in a report instead of your sheet. That aside,

 

you can try using a column reference for your sumif, then subtracting the first seven rows. This will work provided the top 7 rows don't move in your sorting.

Paul Newcome

You could also create a parent row and have rows 7 and down indented underneath of this row. Then you can use the CHILDREN function within your formulas.