Count & Sum Consecutive (2x) appearance of Specific Numeric Values

[Sam via OfficeKB.com]

Hi All,

I have a Named Range called "Data" that spans 8 columns and 7 rows. A Numeric
Value will appear only once in a row. Each row's Numeric Values are in
ascending order.

Is it possible using a single Formula with an Input cell for changing
criterion but without a "helper column" or an "additional table/chart" to
Count and Sum the times Specific Numeric Values appear in a single
consecutive group of 2.

A consecutive value will be in the row below the original value and in any
column; i.e. Col1 Row5 = 101 & Col2 Row6 = 101. This is a count of 1
consecutive group of 2 for Numeric Value 101.


Sample Data Layout:

101 102 107 129 145 370 490 501
101 106 107 128 129 430 470 580
101 129 140 150 350 430 460 470
100 102 129 130 149 330 440 470
101 108 120 129 200 280 430 535
100 101 170 175 176 280 420 520
121 189 170 202 229 230 420 521


Expected Results: (Times Numeric Vales appear in consecutive groups of 2)
Numeric Value 101 = 1
Numeric Value 107 = 1
Numeric Value 129 = 2
Numeric Value 170 = 1
Numeric Value 430 = 1
Numeric Value 280 = 1
Numeric Value 420 = 1

NB: Numeric Value 101 - Col 1 Rows 1,2,3 is a single consecutive count of 3.
Numeric Value 470 - Cols 7,8 Rows 2,3,4 is a single consecutive count of 3.

Thanks
Sam

 

[T. Valko]

Whew!

That diagonal condition is a real killer! I suppose the diagonal can go
either direction, top to bottom, left to right *AND* bottom to top right to
left?

Biff

 

[Sam via OfficeKB.com]

Hi Biff,

Thank you for reply.

I think it would only need to check from top to bottom, left to right; if
that makes it any better.

Cheers,
Sam

Whew!