Excel Array Formula

Question

I've previously created an Excel array formula such as follows:

X13: =SUM(IF($F13>H13:R13,1,0))+IF(F13>F9,1,0)+SUM(IF($F13>T13:U13,1,0))+IF(T13>U13,1,0)

When applying this formula down a column and hitting Ctrl+Shift+Enter, the array formula produced formulas in the rows below as follows:

Y13: =SUM(IF($F14>H14:R14,1,0))+IF(F14>F10,1,0)+SUM(IF($F14>T14:U14,1,0))+IF(T14>U14,1,0)
Z13: =SUM(IF($F15>H15:R15,1,0))+IF(F15>F11,1,0)+SUM(IF($F15>T15:U15,1,0))+IF(T15>U15,1,0)
...

However, now that I'm trying to update the formula, when I hit Ctrl+Shift+Enter, I get the original formula showing up in all cells.

X13: =SUM(IF($F13>H13:R13,1,0))+IF(F13>F9,1,0)+SUM(IF($F13>T13:U13,1,0))+IF(T13>U13,1,0)
Y13: =SUM(IF($F13>H13:R13,1,0))+IF(F13>F9,1,0)+SUM(IF($F13>T13:U13,1,0))+IF(T13>U13,1,0)
Z13: =SUM(IF($F13>H13:R13,1,0))+IF(F13>F9,1,0)+SUM(IF($F13>T13:U13,1,0))+IF(T13>U13,1,0)
...

How do I fix this?

You need to select all the original cells when you alter the formula — Greg Viers
only select the first formula, fix it, hit Ctrl-Shift-enter, then drag it down. do not highlight all of the formula first. — Scott Craner

tigeravatar tigeravatar · Accepted Answer · 2018-03-27T13:57:56

There's no need for an array formula. This formula will perform the exact same functionality and it is not an array formula:

=(F13>F9)+COUNTIF(H13:R13,"<"&$F13)+COUNTIF(T13:U13,"<"&$F13)+(T13>U13)

Excel Array Formula

2 Answers