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

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

Let's see if I understand this...

Only count those rows where the *specific range* is 201 and 207?

So, if a range is 197 to 203 don't include this row even though 201 to 203
falls within the range?
If the range is 202 to 208 don't include this row even though 202 to 207
falls within the range?

Or, DO count those rows?

Sam, your posts are *always* the most complex posts, bar none! <g

I often wonder what kind of application you're working with and if it can be
made simpler!

--
Biff
Microsoft Excel MVP


"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:7ea9029386f78@uwe...
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

Hi Biff,

Thanks for reply.

T. Valko wrote:
Let's see if I understand this...

Only count those rows where the *specific range* is 201 and 207?

No, not quite. The column named "Range" was added just for visual reference
(per 2nd Post: "I've also inserted and extra column called Range just to try
and add a bit of clarity, or maybe not"). The ranges are not blocked together
on each row. The ranges represent individual references ("Refs") in
individual cells, a "Refs" will always have a correponding "Data" value; you
may have "Refs" 201 on one row and "Refs" 207 on a completely different row.

So, if a range is 197 to 203 don't include this row even though 201 to 203
falls within the range?

The range is not hard coded on each row but refers to a group of references
("Refs") that I would like to perform a calculation on as a whole; a summed
count of say any references between 201 to 207 inclusive, with say, a x2
duplicate value of 7 in any "Data" row.

If the range is 202 to 208 don't include this row even though 202 to 207
falls within the range?
The range is not row dependent. Hopefully, explained above.

Or, DO count those rows?
per above.

Sam, your posts are *always* the most complex posts, bar none! <g

In the beginning it all seemed so innocent and logical <bg and then "Bang!"
That squiggy thing in the skull misfired again; you know: that's it....the
"brain"!

I often wonder what kind of application you're working with and if it can be
made simpler!

I think it already has been....my brain! <g

But seriously, aplogies for any confusion.

Hope the above sheds some light.

Further help very much appreciated.

Cheers,
Sam

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

Can you send me a sample file that contains a "smallish" example?

You said your actual file might be ~2000 rows. I don't need that much. Maybe
a hundred or so rows setup *exactly* the way your actual file is setup. Mark
the rows you expect to be counted.

If you can do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

--
Biff
Microsoft Excel MVP


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

Thanks for reply.

T. Valko wrote:
Let's see if I understand this...

Only count those rows where the *specific range* is 201 and 207?

No, not quite. The column named "Range" was added just for visual
reference
(per 2nd Post: "I've also inserted and extra column called Range just to
try
and add a bit of clarity, or maybe not"). The ranges are not blocked
together
on each row. The ranges represent individual references ("Refs") in
individual cells, a "Refs" will always have a correponding "Data" value;
you
may have "Refs" 201 on one row and "Refs" 207 on a completely different
row.

So, if a range is 197 to 203 don't include this row even though 201 to 203
falls within the range?

The range is not hard coded on each row but refers to a group of
references
("Refs") that I would like to perform a calculation on as a whole; a
summed
count of say any references between 201 to 207 inclusive, with say, a x2
duplicate value of 7 in any "Data" row.

If the range is 202 to 208 don't include this row even though 202 to 207
falls within the range?
The range is not row dependent. Hopefully, explained above.

Or, DO count those rows?
per above.

Sam, your posts are *always* the most complex posts, bar none! <g

In the beginning it all seemed so innocent and logical <bg and then
"Bang!"
That squiggy thing in the skull misfired again; you know: that's it....the
"brain"!

I often wonder what kind of application you're working with and if it can
be
made simpler!

I think it already has been....my brain! <g

But seriously, aplogies for any confusion.

Hope the above sheds some light.

Further help very much appreciated.

Cheers,
Sam

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

Hi Biff,

Thanks again for assistance.

T. Valko wrote:
Can you send me a sample file that contains a "smallish" example?

Unfortunately no, there are multiple files involved.

You said your actual file might be ~2000 rows. I don't need that much. Maybe
a hundred or so rows setup *exactly* the way your actual file is setup. Mark
the rows you expect to be counted.

Going back to the file(s) setup. The dynamic range "Refs" and "Data" are
defined as:
In the Define Name Refers To box "Refs":
=OFFSET(Sheet2!$C$76:INDEX(Sheet2!$C$76:$C$2000,MA TCH(9.9E+307,Sheet2!$C$76:
$C$2000))
,0,0,,10)

In the Define Name Refers To box "Data":
=OFFSET(Sheet2!$O$76:INDEX(Sheet2!$O$76:$O$2000,MA TCH(9.9E+307,Sheet2!$O$76:
$O$2000))
,0,0,,10)

The "Refs" numeric values are formula based being pulled from another
worksheet.
The "Data" numeric values are formula based values.

I think this is where the problem lies. Does MMULT operate on cells that
contain formula based values?

Your formula gave me the correct results but my sample was based on numeric
constants and not formula based numeric values.

Would appreciate further help.

Cheers,
Sam

If you can do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

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

Does MMULT operate on cells that contain formula based values?

Yes, that's not the problem.

Another screencap:

http://img176.imageshack.us/img176/5963/sam1mz3.jpg

I used the same dynamic ranges that you posted below except I only use 5
columns instead of 10.

Row 78 is not counted because it does not meet the criteria of count of 7s
= 2, there are 3 in that row.

The only other idea I have as to why you're not getting correct results when
you apply this to your actual data is the possibility of TEXT values in your
data.

=COUNT(refs)=COUNTA(refs) should return TRUE

=COUNT(data)=COUNTA(data) should also return TRUE


--
Biff
Microsoft Excel MVP


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

Thanks again for assistance.

T. Valko wrote:
Can you send me a sample file that contains a "smallish" example?

Unfortunately no, there are multiple files involved.

You said your actual file might be ~2000 rows. I don't need that much.
Maybe
a hundred or so rows setup *exactly* the way your actual file is setup.
Mark
the rows you expect to be counted.

Going back to the file(s) setup. The dynamic range "Refs" and "Data" are
defined as:
In the Define Name Refers To box "Refs":
=OFFSET(Sheet2!$C$76:INDEX(Sheet2!$C$76:$C$2000,MA TCH(9.9E+307,Sheet2!$C$76:
$C$2000))
,0,0,,10)

In the Define Name Refers To box "Data":
=OFFSET(Sheet2!$O$76:INDEX(Sheet2!$O$76:$O$2000,MA TCH(9.9E+307,Sheet2!$O$76:
$O$2000))
,0,0,,10)

The "Refs" numeric values are formula based being pulled from another
worksheet.
The "Data" numeric values are formula based values.

I think this is where the problem lies. Does MMULT operate on cells that
contain formula based values?

Your formula gave me the correct results but my sample was based on
numeric
constants and not formula based numeric values.

Would appreciate further help.

Cheers,
Sam

If you can do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

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

Hi Biff,

Thanks again for further input, most appreciated. As suggested, I've tried
the two formulas below and both return TRUE. If anything else comes to mind
please advise.

Cheers,
Sam

T. Valko wrote:
Does MMULT operate on cells that contain formula based values?

Yes, that's not the problem.

Another screencap:

http://img176.imageshack.us/img176/5963/sam1mz3.jpg

Looks as it should be.

I used the same dynamic ranges that you posted below except I only use 5
columns instead of 10.

Row 78 is not counted because it does not meet the criteria of count of 7s
= 2, there are 3 in that row.

Correct

The only other idea I have as to why you're not getting correct results when
you apply this to your actual data is the possibility of TEXT values in your
data.

=COUNT(refs)=COUNTA(refs) should return TRUE

Returns TRUE

=COUNT(data)=COUNTA(data) should also return TRUE

Returns TRUE

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

Let's assume that the numbers for a row in "Refs" are...

201 202 207 208 210

....and that the corresponding numbers in "Data" are...

7 7 36 35 7

If the criteria is as follows...

=201
<=207
=7

....does it meet the criteria and, therefore, be counted as one?

In article <7ee60c70c2731@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Biff,

Thanks again for further input, most appreciated. As suggested, I've tried
the two formulas below and both return TRUE. If anything else comes to mind
please advise.

Cheers,
Sam

T. Valko wrote:
Does MMULT operate on cells that contain formula based values?

Yes, that's not the problem.

Another screencap:

http://img176.imageshack.us/img176/5963/sam1mz3.jpg

Looks as it should be.

I used the same dynamic ranges that you posted below except I only use 5
columns instead of 10.

Row 78 is not counted because it does not meet the criteria of count of 7s
= 2, there are 3 in that row.

Correct

The only other idea I have as to why you're not getting correct results when
you apply this to your actual data is the possibility of TEXT values in your
data.

=COUNT(refs)=COUNTA(refs) should return TRUE

Returns TRUE

=COUNT(data)=COUNTA(data) should also return TRUE

Returns TRUE

Hi Domenic,

Domenic wrote:
Let's assume that the numbers for a row in "Refs" are...

201 202 207 208 210

...and that the corresponding numbers in "Data" are...

7 7 36 35 7

If the criteria is as follows...

=201
<=207
=7

...does it meet the criteria and, therefore, be counted as one?
Yes.

This qualifies as Refs 201-207 x2 Data duplicate
201,202,207,208,210
7,7,36,35,7

This qualifies as Refs 201-207 x2 Data duplicate
201,202,207,208,210
7,35,7,7,7

This does NOT qualify as Refs 201-207 x2 Data duplicate
201,202,207,208,210
7,7,7,35,7

Cheers,
Sam

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

If that's the case, then everything seems to check out. The formula
that Biff offered should return the desired result.

In article <7ee8a1c97cd54@uwe, "sam518 via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Domenic wrote:
Let's assume that the numbers for a row in "Refs" are...

201 202 207 208 210

...and that the corresponding numbers in "Data" are...

7 7 36 35 7

If the criteria is as follows...

=201
<=207
=7

...does it meet the criteria and, therefore, be counted as one?
Yes.

This qualifies as Refs 201-207 x2 Data duplicate
201,202,207,208,210
7,7,36,35,7

This qualifies as Refs 201-207 x2 Data duplicate
201,202,207,208,210
7,35,7,7,7

This does NOT qualify as Refs 201-207 x2 Data duplicate
201,202,207,208,210
7,7,7,35,7

Cheers,
Sam

At this point, without seeing the actual file(s) I'm out of ideas.

--
Biff
Microsoft Excel MVP


"Domenic" wrote in message
...
If that's the case, then everything seems to check out. The formula
that Biff offered should return the desired result.

In article <7ee8a1c97cd54@uwe, "sam518 via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Domenic wrote:
Let's assume that the numbers for a row in "Refs" are...

201 202 207 208 210

...and that the corresponding numbers in "Data" are...

7 7 36 35 7

If the criteria is as follows...

=201
<=207
=7

...does it meet the criteria and, therefore, be counted as one?
Yes.

This qualifies as Refs 201-207 x2 Data duplicate
201,202,207,208,210
7,7,36,35,7

This qualifies as Refs 201-207 x2 Data duplicate
201,202,207,208,210
7,35,7,7,7

This does NOT qualify as Refs 201-207 x2 Data duplicate
201,202,207,208,210
7,7,7,35,7

Cheers,
Sam

Hi Biff & Domenic

I've been doing a manual count on Refs 201-207 and it tallys with your
Formula result, Biff. I really did expect a much higher count than it turned
out to be! I just didn't believe the results would be as low as they are.

Biff, thank you ever so much for all your time, help and patience.

Cheers,
Sam

Domenic wrote:
If that's the case, then everything seems to check out. The formula
that Biff offered should return the desired result.

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

Glad to hear that. Thanks for the feedback!


--
Biff
Microsoft Excel MVP


"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:7ee9f1c14e28f@uwe...
Hi Biff & Domenic

I've been doing a manual count on Refs 201-207 and it tallys with your
Formula result, Biff. I really did expect a much higher count than it
turned
out to be! I just didn't believe the results would be as low as they are.

Biff, thank you ever so much for all your time, help and patience.

Cheers,
Sam

Domenic wrote:
If that's the case, then everything seems to check out. The formula
that Biff offered should return the desired result.

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