Try this:

=SUMPRODUCT(--(MMULT((ISNUMBER(data))*(data=0),{1;1;1;1;1})=2))

That's based on your sample using 5 columns. For your real data with 8
columns change:

{1;1;1;1;1}

to:

{1;1;1;1;1;1;1;1}

Note this is limited to ~5400 rows of data.


--
Biff
Microsoft Excel MVP


"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:8208d647695af@uwe...
Hi JP,

I kept checking for replies yesterday but nothing showed up? Thank you
very
much for your reply and assistance.

Not looking for duplicates as such but rather a summed count of a
particular
number that appears twice and only twice in a row but can have multiple x2
appearances within my dynamic range.

Just a brief recap:
I would like to find the summed count of a specific (but variable) numeric
value that appears only twice (x2) in any row; the numeric value can
appear
in any column. Would very much appreciate a formula using the dynamic
named
range "Data" as opposed to the A1 notation style of cell referencing.

The criterion in this instance is "0" zero.

Sample Data Layout (using 5 columns, 8 rows):
0 89 0 100 0
216 100 205 70 220
0 216 218 0 206
343 99 250 323 256
234 0 211 99 214
219 134 235 500 243
205 0 0 150 214
99 0 250 0 0

Expected Results:
Looking for criterion "0": a summed count of 2 should be returned.
Rows 3 and 7 meet the criteria: explicit x2 appearance of zero (0) in any
row.


Further help appreciated, if possible.

Cheers,
Sam

JP wrote:
If your range was named "myData", this formula would count the number
of times the number 1 appears in the range.

=IF(COUNTIF(myData,1)1,"Dupes!","No Dupes")

And this formula (entered as an array w/ Ctrl-Shift-Enter), will let
you know if there are any dupes at all:

=IF(COUNTA(myData)=SUM(1/COUNTIF(myData,myData)),"All Unique","Some
dupes")

Does this help?
Not looking for duplicates as such, please see above.

--JP

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