Formula with ROWS Function appears not to return correct number of rows

[rzw30]

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

[Kevin UK]

Hi rzw30

Might be a good idea to upload a sample file with dummy data, and your expected result.

[arlu1201]

rzw30,

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[rzw30]

Thanks for the advice, Kevin. I have uploaded a sample sheet and changed the title of my original post

rgds

Richard (rzw30)

[Kevin UK]

Hi rzw30

Sorry, but what is the result that you require.

[daddylonglegs]

There's a difference between entering an array formula in a single cell and copying and entering in a whole range - you have done the latter so the ROWS part doesn't increment. Try deleting the whole range of current formulas then inputting this version in the first cell (A106)

=IFERROR(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)),COLUMNS($A106:A106)),"")

Now confirm with CTRL+SHIFT+ENTER

....and only then copy across and down - you should only see one row of data - the rest will be blank

[rzw30]

AMdaddylonglegs

Thanks so much, it's the first time with array formulae I should have read more before I tried to use them

regards

Richard

[rzw30]

Thanks kevin for your help