Reference cell value in an array formaula

[SimoEx]

Hi,

I have an array formula to count unique values in a column between cells. The range is determined by the date.

At the moment, the start date and the last date is determined by finding the first cell and last cell by searching by criteria. Basically I want to count the unique vales over each 12 month period, and the records are constantly being added to. At the moment there are 5 years of records, but often records are added "posthumously".

This array works:
=SUM(IF(FREQUENCY(IF(LEN(A2:A1187)>0,MATCH(A2:A1187,A2:A1187,0),""), IF(LEN((A2:A1187))>0,MATCH(A2:A1187,A2:A1187,0),""))>0,1))

The trouble with this, if data is added in the range, the last cell might change to (say) A1200, and this means the array needs to be changed each time records are added (or deleted).

In the following example the value of CELL Y5 = "A2" and CELL Y6 = "A1187"

This array works too:
=SUM(IF(FREQUENCY(IF(LEN((Y5) & ":" & (Y6))>0,MATCH(A2:A1187,A2:A1187,0),""), IF(LEN((Y5) & ":" & (Y6))>0,MATCH(A2:A1187,A2:A1187,0),""))>0,1))

Note that instead of A2:A1187 I am now picking up the values in Y5 and Y6

I would have thought then that this would work:
=SUM(IF(FREQUENCY(IF(LEN((Y5) & ":" & (Y6))>0,MATCH((Y5) & ":" & (Y6),(Y5) & ":" & (Y6),0),""), IF(LEN((Y5) & ":" & (Y6))>0,MATCH((Y5) & ":" & (Y6),(Y5) & ":" & (Y6),0),""))>0,1))

....but it won't.

What is wrong with my syntax?

Cheers!

Michael

 

[XOR LX]

Hi.

Unfortunately what you believe to be constructing an actual range reference is actually just generating a text string.

The part:

(Y5)&":"&(Y6)

resolves to the text string:

"A2:A1187"

and not the range reference:

A2:A1187

Hence, when passed to LEN:

LEN((Y5)&":"&(Y6))

which is:

LEN("A2:A1187")

is always 8, and so:

LEN("A2:A1187")>0

is always TRUE, though of course this is not at all what you want, since you wish this test to be performed on all 1186 cells in the range A2:A1187.

In order to convert the above text string to an actual range reference, you would need to use INDIRECT, i.e.:

INDIRECT(Y5&":"&Y6)

(You don't need your additional parentheses around the range references.)

However, since INDIRECT is a volatile function, my preference would be to enter in Y5 and Y6 - not A2 and A1187 - but simply 2 and 1187, after which you can use the less volatile:

=SUM(IF(FREQUENCY(IF(LEN(INDEX(A:A,Y5):INDEX(A:A,Y6))>0,MATCH(A2:A1187,A2:A1187,0)),MATCH(A2:A1187,A2:A1187,0))>0,1))

I've also tidied up other parts of your formula. The LEN clause does not need to be repeated and there is no need for a FALSE clause in the IF statement.

Regards