|
Return Summed Count of Multiple Consecutive Numeric Values
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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200604/1 |
Return Summed Count of Multiple Consecutive Numeric Values
You might want to try using COUNTIF with the range selected as desired.
"Sam via OfficeKB.com" wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200604/1 |
Return Summed Count of Multiple Consecutive Numeric Values
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 Barb Reinhardt wrote: You might want to try using COUNTIF with the range selected as desired. Hi All, [quoted text clipped - 57 lines] Cheers, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200604/1 |
Return Summed Count of Multiple Consecutive Numeric Values
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! In article <5e9315c8ceab0@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: 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 |
Return Summed Count of Multiple Consecutive Numeric Values
Hi Domenic,
Thank you very much - solution worked a treat - Brilliant! Cheers, Sam Domenic wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200604/1 |
| All times are GMT +1. The time now is 01:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com