# Reference cell value in an array formaula

#### SimoEx

##### New Member
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

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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)

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

Replies
0
Views
201
Replies
5
Views
155
Replies
10
Views
633
Replies
2
Views
263
Replies
0
Views
128

### Forum statistics

1,203,052
Messages
6,053,223
Members
444,648
Latest member
sinkuan85 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back