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

 

[konew1]

You might try putting =INDIRECT function around them. Something like
MATCH(indirect(Y5 & ":" & Y6) instead of MATCH((Y5) & ":" & (Y6)

I have also removed surplus ()

 

[SimoEx]

Ah ah! INDIRECT - thank you. Works perfectly now.

Can you explain INDIRECT?

Cheers!

mcihael

 

[konew1]

Glad to help

Indirect lets you reference one cell that contains all or part of a cell address that you really want to reference. You held A2 and A1187 in Y5 and Y6 so you can easily change the range that you need to access, without changing all of the formulas.

While I didn't replicate or test your formula, I was surprised that the formula in your first posting worked

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

Given that Y5 holds A2 and Y6 holds A1187 I would have expected IF(LEN((Y5) & ":" & (Y6))>0, to evaluate as
IF(LEN("A2"&":"&"A1187")>0,
becomes IF(LEN("A2:A1187")>0,
Becomes IF(8>0,... which will always be true.
The references to Y5 and Y6 will return the text A2 and A1187, not interpreted as cell addresses.

I didn't raise this because you say the formula works, and I don't have experience with FREQUENCY function and I am not expert with array functions.