Sum Count of Numeric Duplicates: appears x2 in any Row

[Sam via OfficeKB.com]

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

 

[T. Valko]

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

 

[JP]

Phew, I thought I was the only one.

--JP

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

--
Biff
Microsoft Excel MVP









- Show quoted text -

 

[Sam via OfficeKB.com]

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

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

Hope sample helps.

 

[T. Valko]

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

 

[Sam via OfficeKB.com]

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

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.

 

[T. Valko]

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
here: {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.

 

[Sam via 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]

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!

 

[Sam via OfficeKB.com]

Hi Biff,

Thanks for reply.

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

 

[T. Valko]

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.

 

[Sam via OfficeKB.com]

Hi Biff,

Thanks again for assistance.

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,MATCH(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,MATCH(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:

 

[T. Valko]

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

 

[Sam via OfficeKB.com]

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

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

 

[Domenic]

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?

 

[sam518 via OfficeKB.com]

Hi Domenic,

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...
<=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

 

[Domenic]

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

 

[T. Valko]

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

 

[Sam via OfficeKB.com]

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

 

[T. Valko]

Glad to hear that. Thanks for the feedback!