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?

Best,

Michaël

Comments

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

    thinkspi.com

Sign In or Register to comment.