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.

--
Biff
Microsoft Excel MVP


"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:7ea59b85863e7@uwe...
Hi Biff,

Thank you very much for your time and assistance. Your formula does
provide
the correct result.

However, as new data is continually added, I'm using dynamic named ranges.
Is
it possible for you to provide a formula solution using the named ranges
as
opposed to actual cell references?

Very much appreciated.

Cheers,
Sam

T. Valko wrote:
I'm not real sure but this works on your sample data. (not extensively
tested!)

First thing though. you need to split the "range" into 2 cells.

See this screencap:

http://img165.imageshack.us/img165/5903/samli7.jpg

--
Message posted via http://www.officekb.com