Hi,
TL;DR; Copying cells down one cell and updating references no longer matches text as it did in first row of cells.
I have a set of cells that I wanted to cut/paste (or copy/paste/delete first row) so that I could still use a sumif statement to read across the top and and information relevant from those columns as it had before.
Starting in P11 and going to AL11 I have a number of columns (Using Bold), thus
Placement |Site | Rating | Placement |Site | Rating | Placement |Site | Rating
In F11 I have (In a spanning cell, Using bold):
Placement
In F13 I have a formula:
=SUMIF($P$11:$AL$11,$F$11,$P13:$AL13)
This All Works - All columns information in the row that have a header of “Placement” are added together.
I then Cut/Paste P11:AL11 to P12:AL12. Then I re-visit F13 and change the formula to:
=SUMIF($P$12:$AL$12,$F$11,$P13:$AL13)
Then fill-down (via drag or CTL-D)The first row sums to 1 (first number it sees under a placement). The second row, previously, summed to 16 - but is now #N/A (as are all following rows).
Here is a copy/paste of row 18 (that now reads #N/A):
=SUMIF($P$12:$AL$12,$F$11,$P18:$AL18)
Now if I copy/paste P12:AL12 back into P11:AL11 all the numbers sum as they should under each heading of Placement.
If I delete P12:AL12, now F13 breaks and F14 sums only the first number it sees under a “Placement”. All other cells turn to 0.
I thought maybe its something to do with range/column naming and have checked under Insert > Named Expressions > [Everything]. But there are no names or defined column headings.
Version was 5.2.2.2
Update to try to fix to 5.2.3.3 (x64)
I have tried multiple “Paste Special” options, including all. As well as copying/pasting each cell down individually.
Any thoughts on this? Thanks!