# 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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
You might try putting =INDIRECT function around them. Something like
MATCH(indirect(Y5 & ":" & Y6) instead of MATCH((Y5) & ":" & (Y6)

I have also removed surplus ()

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

Can you explain INDIRECT?

Cheers!

mcihael

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.

Replies
2
Views
597
Replies
3
Views
234
Replies
0
Views
253
Replies
0
Views
284
Replies
3
Views
475

### Forum statistics

1,196,152
Messages
6,013,746
Members
441,781
Latest member
Gian Carlos ### 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