I am trying to get right a complex formula in excel 2013 and the issue seems to be that the ROWS function at the end of the function fails to count the number of rows because the $ and the absence of the $ are not working, and the Rows formula is always returning the 1st row of the array.
The complete expression is
=INDEX(input, SMALL(IF((INDEX(input, ,$O$103, 1)<=$O$101)*(INDEX(input, ,$O$103, 1)>=$O$102), ROW(input)-MIN(ROW(input))+1, ""), ROWS(A$106:A106)),, 1)
Input is an array referenced by the above formula which is entered as an array formula further down the sheet. The objective is to return rows which match specific criteria defined as constants in the sheet ($O$103=1 representing column 1 of the array, $O$101 and $O$102 are alphabetic and define the criteria to return the row (e.g. rows with contents between AB and CD). The problem is that the ROWS function ALWAYS returns 1 (being the number of rows. The expression does not change further down the sheet i.e. ROWS(a106:a106).
The above expression is entered as an array formula
I have uploaded a spreadsheet created in excel 2013 with this problem
Any help would be more than appreciated
richard
Bookmarks