Thread: Formula error:bring up an #VALUE! Error.

Hello,

Currently working a spreadsheet where i am using the follow Formula that is bring up an #VALUE! Error.

=IF(ROWS(C$90:C90)<=SUM(N(Availability!C$1256:C$1305<>"")),INDEX(Availability!C$1256:C$1305,MATCH(SM ALL(COUNTIF(Availability!C$1256:C$1305,"<="&Availability!C$1256:C$1305),SUM(N(Availability!C$1256:C$ 1305=""))+ROWS(Availability!$C$90:C$90)),COUNTIF(Availability!C$1256:C$1305,"<="&Availability!C$1256 :C$1305),0)),"")

Using the same formula for actions in the spreadsheet but this one seems to be throwing out an error. Can anyone assist?

Thanks.

That's an "array formula". Did you confirm with CTRL+SHIFT+ENTER so that curly braces appear around the formula?

Also with that version I think you'd keep getting the same results as you copy the formula down, I think the second ROWS function should be the same as the first, i.e. I think the red part should change

=IF(ROWS(C$90:C90)<=SUM(N(Availability!C$1256:C$1305<>"")),INDEX(Availability!C$1256:C$1305,MATCH( SMALL(COUNTIF(Availability!C$1256:C$1305,"<="&Availability!C$1256:C$1305), SUM(N(Availability!C$1256:C$ 1305=""))+ROWS(Availability!$C$90:C$90)),COUNTIF(Availability!C$1256:C$1305,"<="&Availability!C$1256 :C$1305),0)),"")

to this

=IF(ROWS(C$90:C90)<=SUM(N(Availability!C$1256:C$1305<>"")),INDEX(Availability!C$1256:C$1305,MATCH( SMALL(COUNTIF(Availability!C$1256:C$1305,"<="&Availability!C$1256:C$1305), SUM(N(Availability!C$1256:C$ 1305=""))+ROWS(C$90:C90)),COUNTIF(Availability!C$1256:C$1305,"<="&Availability!C$1256 :C$1305),0)),"")