SUM function entered as an array formula will not sum the array, but will when entered as a standard formula

[Give Me Arrays]

Hi All,

Very familiar with the ins/outs of using complex array formulas, but found the strangest issue with SUM not summing the numerical contents of the reduced array, e.g., =SUM({0,0,1,0,0})...and this produces a zero. However, it works as intended by entering it as a standard formula (just pressing enter). At first I thought this was maybe a new 365 calc engine issue, but then I found a 2009 post ("Why does wrapping an Excel array formula in a SUM not produce the sum of the contents in this case?") on another common forum (stackoverflow) with the same issue (never actually answered), which I replicated in 365 to the same effect.

Formula structure as follows: =SUM(N(ISNUMBER(MATCH(range1,IFERROR(INDEX(range2,MATCH(range3,ref,0)*ascending.numeric.range),""),0))))

What's especially peculiar is that if you enter the formula as an array formula and you add ">0" at the end, it passes off the sum of the array to calculate "1>0" and it evaluates it as FALSE. Even if you force "1*1>0" to do a paranoid sanity check on the 1 passing to the ">0" calc. This really has me puzzled.

Questions:

1. Is an array formula entered this way (just enter) reliable?
2. It's been tested and works but should I expect it to fail at some point?
3. Is this formula backwards compatible without entering it as an array formula?
4. Can someone please explain whether this is a known issue with a definable reason?

Thank You,
Give Me Arrays

 

[jasonb75]

I haven't tested the longer structure due to the absence of a suitable data set but the simple version, =SUM({0,0,1,0,0}) works correctly for me with office 365 whether array confirmed or not.

 

[pjmorris]

As per Jason's reply - the simple version works whether array confirmed or not Office 2016.

 

[Give Me Arrays]

I haven't tested the longer structure due to the absence of a suitable data set but the simple version, =SUM({0,0,1,0,0}) works correctly for me with office 365 whether array confirmed or not.

Correct, it appears there's something about the ctrl+shift+enter wrap that's causing the issue in the larger formula. If a small mock dataset is needed, I can generate that.

 

[Give Me Arrays]

As per Jason's reply - the simple version works whether array confirmed or not Office 2016.

Correct, the issue appears when you add the larger formula contents which work like a join and seem to nullify the SUM function; then only works (at least for now) by pressing enter only.

 

[jasonb75]

I can't remember all of the details without setting up tests to confirm them, but in short (for formulas that do not spill) when you confirm it with ctrl shift enter in 365, it would be the same as confirming an array to multiple cells at the same time so that each cell returns a different element of the array. When it is entered without ctrl shift enter it would be the same as committing the array formula to a single cell in an older version.

When opened in an older version, it will convert based on the above, so should return the same result.