Sorting and conserving formula

08/30/19 Edited 12/09/19

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?




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

Sign In or Register to comment.