Hi Biff,

Thank you for further input. When I use the formula below on the very small
sample data it provides the correct result. However, when I apply it to the
real data (2000 rows, 10 columns), amending the columns and rows; I do not
get the expected results. The counts are much lower than they should be.

Formula used on Sample Data using 3 columns each for "Refs" and "Data":
=SUMPRODUCT(--(MMULT(--(Refs=D2)*(Refs<=E2)*(Data=7),{1;1;1})=2))

Range is 201-207; columns D2 and E2 respectively.

In the Define Name Refers To box "Refs":
=OFFSET(Sheet2!$A$2:INDEX(Sheet2!$A$2:$A$10,MATCH( 9.9E+307,Sheet2!$A$2:$A$10))
,0,0,,3)

In the Define Name Refers To box "Data":
=OFFSET(Sheet2!$F$2:INDEX(Sheet2!$F$2:$F$10,MATCH( 9.9E+307,Sheet2!$F$2:$F$10))
,0,0,,3)

Sample Data:
Refs Refs Refs Range Range Data Data Data
201 205 206 201 207 5 7 7
216 218 220 215 221 13 8 13
243 250 256 250 256 23 53 20
209 211 214 208 214 54 6 54
234 235 243 229 235 84 34 84
205 207 214 201 207 7 7 4


I'm clutching at straws, haven't a clue why it's not working on the live data.
Does it need ROW(Data)-MIN(ROW(Data)) ?

Further assistance most appreciated.

Cheers,
Sam

T. Valko wrote:
Just replace the references with the named ranges. You can name the "range"
like: Rng1 and Rng2.

You mentioned that your actual data was 10 columns wide so you need 10 ones
he {1;1;1;1;1;1....}. This could be calculated (adding compexity and
makes the formula an array and longer) but since the number of columns is
relatively small I'd just use the array constant.

=SUMPRODUCT(--(MMULT((refs=rng1)*(refs<=rng2)*(data=7),{1;1;1;1 ;1;1;1;1;1;1})=2))

Also note, the MMULT function is limited to no more than 5460 rows. If your
data will exced that limit then it's back to the drawing board and will
probably need a helper column.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200801/1