Merging Sumproduct and Frequency Formulas/Functions

[mrgreek]

Hi everyone,

I have a frequency formula in an array:

=SUM(IF(FREQUENCY(IF(LEN(Data!K488:K520)>0,MATCH(Data!K488:K520,Data!K488:K520,0),""), IF(LEN(Data!K488:K520)>0,MATCH(Data!K488:K520,Data!K488:K520,0),""))>0,1))

And I have a column J that has corresponding dates beside column K. I would like to have the formula spit out a value that is based on a customizable date range. That is, expand K488 and K520 (say K5 to K 600) and have a sumproduct or some other formula which makes the array look at column J and 2 dates in 2 other cells, so that it is customizable for that date range.

If I wasn't clear, please let me know. Any help is greatly appreciated.

Thanks

[NBVC]

Assuming your formula is in Sheet3,

First assuming you enter your desired start and end row numbers for the range in X1 and X2 of Sheet3, then in the Data sheet, go to Insert|Name|Define and enter a name like: Data and then enter a formula in the Refers to box:

=INDEX(Data!$A:$A,Sheet3!$X$1):INDEX(Data!$L:$L,Sheet3!$X$2)

which assumes the columns in the database are from column A to column L, you can adjust those...

then in sheet3, assuming the start and end dates would be in Z1 and Z2 use formula:

=SUM(IF(FREQUENCY(IF((LEN(INDEX(Data,0,11))>0)*(INDEX(Data,0,10)>=Z1)*(INDEX(Data,0,10)<=Z2),MATCH(INDEX(Data,0,11),INDEX(Data,0,11),0),""), IF((LEN(INDEX(Data,0,11))>0)*(INDEX(Data,0,10)>=Z1)*(INDEX(Data,0,10)<=Z2),MATCH(INDEX(Data,0,11),INDEX(Data,0,11),0),""))>0,1))

CSE confirmed

[mrgreek]

Perfect,

Much appreciated, thanks!

[mrgreek]

douple post... sorry

[NBVC]

You are welcome.

Please try to remember to mark your thread as SOLVED once satisfied.