Hi All,

I have two dynamic named ranges: "Data" and "Refs", each spanning 10 columns
and the same number of rows. They both hold numeric values.

Using input cells for the varying criteria, I would like to find the summed
count of a criterion that appears twice (x2) in any single row of "Data". The
count will be qualified and restricted by a range of values within "Refs".
Sample "Refs" values are 201, 202, 203, 204, 205, 206, 207-1000+. The "Refs"
values will be used in a sequential single group / block of 7 but the actual
range will vary; i.e. 229-235, 250-256 or 257-263 etc.

Required Solution:
Input cell for "Data" criterion
Input cell(s) for "Refs" criteria (varying sequential group of 7 values)
Sum the count of "Data" criterion that appears twice in any row of "Data" and
is within the numeric range 207-214 in "Refs".

If possible, I would like a flexible formula that does not require filling
down alongside the original data.
The summed count should be returned to a single cell on a different worksheet
to where the actual data is held.

Thanks
Sam

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

I'm pretty sure no one understands what you want. How about a sample and the
expected result.

--
Biff
Microsoft Excel MVP


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

I have two dynamic named ranges: "Data" and "Refs", each spanning 10
columns
and the same number of rows. They both hold numeric values.

Using input cells for the varying criteria, I would like to find the
summed
count of a criterion that appears twice (x2) in any single row of "Data".
The
count will be qualified and restricted by a range of values within "Refs".
Sample "Refs" values are 201, 202, 203, 204, 205, 206, 207-1000+. The
"Refs"
values will be used in a sequential single group / block of 7 but the
actual
range will vary; i.e. 229-235, 250-256 or 257-263 etc.

Required Solution:
Input cell for "Data" criterion
Input cell(s) for "Refs" criteria (varying sequential group of 7 values)
Sum the count of "Data" criterion that appears twice in any row of "Data"
and
is within the numeric range 207-214 in "Refs".

If possible, I would like a flexible formula that does not require filling
down alongside the original data.
The summed count should be returned to a single cell on a different
worksheet
to where the actual data is held.

Thanks
Sam

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

Phew, I thought I was the only one.

--JP

On Jan 22, 2:35*pm, "T. Valko" wrote:
I'm pretty sure no one understands what you want. How about a sample and the
expected result.

--
Biff
Microsoft Excel MVP

"Sam via OfficeKB.com" <u4102@uwe wrote in messagenews:7e97b221dfca8@uwe....



Hi All,

I have two dynamic named ranges: "Data" and "Refs", each spanning 10
columns
and the same number of rows. They both hold numeric values.

Using input cells for the varying criteria, I would like to find the
summed
count of a criterion that appears twice (x2) in any single row of "Data"..
The
count will be qualified and restricted by a range of values within "Refs".
Sample "Refs" values are 201, 202, 203, 204, 205, 206, 207-1000+. The
"Refs"
values will be used in a sequential single group / block of 7 but the
actual
range will vary; i.e. 229-235, 250-256 or 257-263 etc.

Required Solution:
Input cell for "Data" criterion
Input cell(s) for "Refs" criteria (varying sequential group of 7 values)
Sum the count of "Data" criterion that appears twice in any row of "Data"
and
is within the numeric range 207-214 in "Refs".

If possible, I would like a flexible formula that does not require filling
down alongside the original data.
The summed count should be returned to a single cell on a different
worksheet
to where the actual data is held.

Thanks
Sam

--
Message posted viahttp://www.officekb.com- Hide quoted text -

- Show quoted text -

Hi Biff,

Just got back to Post. Huge apology. I didn't understand it either without
the sample data! Sorry.

Any help most appreciated.

I've just included a small sample, 3 columns for "Refs" and "Data" rather
than the 10 columns.
I've also inserted and extra column called Range just to try and add a bit of
clarity, or maybe not.

The ranges "Refs" and "Data" mirror each other in that they have the same
number of rows and columns. Also, each "Refs" cell relates to a corresponding
"Data" cell value.

For example, the first row of sample data:
Refs 201 corresponds to Data value 5
Refs 205 corresponds to Data value 7
Refs 206 corresponds to Data value 7

Sample Data Layout:
Refs Refs Refs 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

Example Scenario:
I would like to sum the count of a specific but changeable x2 duplicate
criterion in any row of "Data" within a specific but also changeable "Refs"
range. That is, sum the count of all "Data" rows with a x2 duplicate
criterion value of 7 within Refs range 201-207.

Expected Result:
The result should be a summed count of 2.
The first row has x2 criterion 7 in "Data" and their corresponding "Refs" are
205 and 206, both within range 201-207.
The last row also has x2 criterion 7 and their corresponding "Refs" are 205
and 207, both within range 201-207.

Cheers,
Sam

T. Valko wrote:
I'm pretty sure no one understands what you want. How about a sample and the
expected result.
Hope sample helps.

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

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

--
Biff
Microsoft Excel MVP


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

Just got back to Post. Huge apology. I didn't understand it either without
the sample data! Sorry.

Any help most appreciated.

I've just included a small sample, 3 columns for "Refs" and "Data" rather
than the 10 columns.
I've also inserted and extra column called Range just to try and add a bit
of
clarity, or maybe not.

The ranges "Refs" and "Data" mirror each other in that they have the same
number of rows and columns. Also, each "Refs" cell relates to a
corresponding
"Data" cell value.

For example, the first row of sample data:
Refs 201 corresponds to Data value 5
Refs 205 corresponds to Data value 7
Refs 206 corresponds to Data value 7

Sample Data Layout:
Refs Refs Refs 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

Example Scenario:
I would like to sum the count of a specific but changeable x2 duplicate
criterion in any row of "Data" within a specific but also changeable
"Refs"
range. That is, sum the count of all "Data" rows with a x2 duplicate
criterion value of 7 within Refs range 201-207.

Expected Result:
The result should be a summed count of 2.
The first row has x2 criterion 7 in "Data" and their corresponding "Refs"
are
205 and 206, both within range 201-207.
The last row also has x2 criterion 7 and their corresponding "Refs" are
205
and 207, both within range 201-207.

Cheers,
Sam

T. Valko wrote:
I'm pretty sure no one understands what you want. How about a sample and
the
expected result.
Hope sample helps.

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

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

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