Return Summed Count of Multiple Consecutive Numeric Values

[Sam via OfficeKB.com]

Hi All,

Return Summed Count of Multiple Consecutive (Duplicate) Numeric Values in
separate Columns -

I would like a Formula to Sum the Count of all Consecutive Doublets(2),
Triplets(3), Quadruplets(4), Quintuplets(5), Sextuplets(6), Septuplets(7) and
Octuplets(8) for specific Numeric Values and Return the Results to their
respective columns in my Results Table.

Data Table Layout:
Each Numeric value will be housed in its own separate column, so Numeric
Value 50 will only be in Column "E", Numeric Value 54 will only be in Column
"F" etc.

The Results Table will house the summed count of each Numeric Values'
CONSECUTIVE Doublets(2), Triplets(3), Quadruplets(4), Quintuplets(5),
Sextuplets(6), Septuplets(7) and Octuplets(8) appearance.

Results Table Layout:
Column "A" has individual unique Numeric Values on each Row .
Columns "B"-"H" House Consecutive Counts for each Numeric Value; "B"=Doublets
(2), "C"=Triplets(3), "D"=Quadruplets(4), "E"=Quintuplets(5), "F"=Sextuplets
(6), "G"=Septuplets(7) and "H"=Octuplets(8).

Sample Data - Data Table:
RowNo. Col "E" Col "F"
19 50 54
20 blank 54
21 50 54
22 50 blank
23 50 blank
24 blank blank
25 50 54
26 blank 54
27 50 blank
28 50 blank
29 blank 54
30 50 blank
31 50 blank
32 50 blank
33 50 blank
34 blank 54

The Blank Rows are the result of a Formula's "empty text".

Criteria for Counting Consecutive appearances of Numeric Values:
A Doublet is denoted by no more than two individual Consecutive appearances
of a Numeric Value separated by any number of Blank Cells. A Triplet is
denoted by no more than three individual Consecutive appearances of a Numeric
Value separated by any number of Blank Cells etc. A consecutive count ends
when a blank (empty text) cell appears.

Expected Results - Results Table:
Col "A" Col "B" Col "C" Col "D"
Numeric Value Doublets(2) Triplets(3) Quadruplets(4)
50 1 1 1
54 1 1 0

Cheers,
Sam

 

[Guest]

You might want to try using COUNTIF with the range selected as desired.

 

[Sam via OfficeKB.com]

Hi Barb,

Not quite sure what you mean?

Can you give me an example using my data of how this will work with my
scenario?

Much appreciated.
Cheers,
Sam

You might want to try using COUNTIF with the range selected as desired.

Hi All,

[quoted text clipped - 57 lines]

Cheers,
Sam

 

[Domenic]

Let G18:H18 contain 50 and 54

G19, copied across:

=IF(E19=G$18,1,0)

G20, copied down and across:

=IF(E20=G$18,G19+1,0)

Let J18:L18 contain 2, 3, and 4

Let I19:I20 contain 50 and 54

J19, copied down and across:

=SUMPRODUCT(--(OFFSET($G$19:$G$34,0,MATCH($I19,$G$18:$H$18,0)-1)=J$18),--
(OFFSET($G$19:$G$34,1,MATCH($I19,$G$18:$H$18,0)-1)=0))

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much - solution worked a treat - Brilliant!

Cheers,
Sam

Let G18:H18 contain 50 and 54

G19, copied across:
=IF(E19=G$18,1,0)

G20, copied down and across:
=IF(E20=G$18,G19+1,0)

Let J18:L18 contain 2, 3, and 4
Let I19:I20 contain 50 and 54

J19, copied down and across:
=SUMPRODUCT(--(OFFSET($G$19:$G$34,0,MATCH($I19,$G$18:$H$18,0)-1)=J$18),--
(OFFSET($G$19:$G$34,1,MATCH($I19,$G$18:$H$18,0)-1)=0))

Hope this helps!

Hi All,

[quoted text clipped - 57 lines]

Cheers,
Sam