I have an array formula whose answer I cannot explain.

Consider the following data layout in A1:G1

A B C D E F G
1 3 5 6

The array formula

=IF(ISNUMBER(A1:G1),A1:G1,OFFSET(A1,0,MATCH(COLUMN (),IF(ISNUMBER(A1:G1),COLUMN(A1:G1)))-1))

returns

1 3 3 3 5 5 6

The sum of these values is 26, but SUM() of the array formula is 33; why?



Here are the diagnostics that I have tried (all are to be array entered)

=ROWS(IF(ISNUMBER(A1:G1),A1:G1,OFFSET(A1,0,MATCH(C OLUMN(),IF(ISNUMBER(A1:G1),COLUMN(A1:G1)))-1)))

returns 1, as expected.

=COLUMNS(IF(ISNUMBER(A1:G1),A1:G1,OFFSET(A1,0,MATC H(COLUMN(),IF(ISNUMBER(A1:G1),COLUMN(A1:G1)))-1)))

returns 7, as expected, yet

=SUM(IF(ISNUMBER(A1:G1),A1:G1,OFFSET(A1,0,MATCH(CO LUMN(),IF(ISNUMBER(A1:G1),COLUMN(A1:G1)))-1)))

returns 33 instead of 26.


Any thoughts?

Jerry