Formula is changing after a date sort

stever9240 · 02/04/18

Once my data is in the appropriate cells I want to perform a sort by Due Date. When I do this

the formula

=SUMIF($Equipment5:$Equipment171, "VUTEK", $Tue5:$Tue171)

Changes to

=SUMIF($Equipment16:$Equipment171, "VUTEK", $Tue16:$Tue171)

Thus not including any data from rows 4 thru 15.

I have locked the row with the formulas but does not work.

J. Craig Williams · 02/04/18

Without seeing the organization of your data, here's some tips:

Sorting will mess up references.

If you have Absolute References ($Column$Row) then if the row is not first or last, the range will shrink.

Try either:

1. Using the whole column

=SUMIF(Equipment:Equipment, "VUTEK", Tue:Tue)

2. Using hierarchy and summing the children

=SUMIF(CHILDREN(Equipment4), "VUTEK", CHILDREN(Tue4)

(assuming row 4 is the parent)

I hope this helps.

Craig

J. Craig Williams · 02/08/18

Check out the @row feature.

Craig

Comments