2

I've just discovered that with a specific situation, the =SUM(ABOVE) formula will misbehave... At least it's misbehaving as far as I can tell...

Produce a 3x3 table, with the bottom-left two cells merged and fill like so:

table layout

Believe it or not, that equation will come out to 5... 2 + 3.

table result

I've confirmed this by changing those values, and the issue appears to be exacerbated by more merged columns.

The following will all result in the correct 3 being produced:

  • Remove the merge
  • Remove the top row
  • Insert a row directly above - note: with or without a number in it
  • Insert a number into the top row
  • Insert an empty column between the 2 and 3 - note: without a number in it

Am I missing something, or do we have to be very careful when using merged cells and formulae?

Is there something more correct than ABOVE to use here?

I'm using Word 2013.

My solution for now is to simply avoid merged cells, and keep the column counts the same throughout - even if they aren't aligned:

misaligned columns

Improve this question

1 Answer 1

Reset to default
5

Yes, merged cells will mess with its mind. An alternative to use specific cell numbers.

enter image description here

If you want to mix the use of "ABOVE" with explicit cell references the formula could look like:

{ =SUM(ABOVE+A2) } and in the above example the summed value would become 4. Simple addition or subtraction can be used in this way. There is a PRODUCT function for multiplication and division.

The ABOVE function has some limitation, including stopping when a blank cell or a cell containing text is encountered. For further information on summing an entire column of numbers and working around the function's limitations, here is a link to an article on the Word MVP website entitled, "How to get a formula to total an entire table column, even if some cells in the column contain text or are blank."

Improve this answer
5
  • 1
    I didn't realise that was an option - thanks! Is there an easy way to do the similar ("everything above this cell") with explicit cell references?
    – Attie
    Aug 31, 2018 at 17:26
  • @Attie I've added another example that hopefully answers your question.
    – Rich Michaels
    Aug 31, 2018 at 21:42
  • Thanks @Rich... I was really wondering if there was a "use all the cells above this row, in Column C"... akin to C1:C2 that would grow if rows were inserted - e.g: becoming C1:C3.. this doesn't appear to happen automatically, instead behaving more like $C$1:$C$2
    – Attie
    Aug 31, 2018 at 21:53
  • @Attie the ABOVE function is not a static range. The range grows as rows are added, as long as the content of the cells above are numeric.
    – Rich Michaels
    Sep 1, 2018 at 2:00
  • I've just found out today that Excel plays poorly with merged cells and references too... sigh
    – Attie
    Oct 1, 2018 at 18:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .