Need A Count Formula

C

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,870
The values in columns B:D range from 0-9. Need a formula that will count the number of rows that it takes for all 10 values to appear, in same column.
Excel Workbook
ABCDEFG
103/21/11005101112
203/20/11114
303/19/11226
403/18/11337
503/17/11449
603/16/11558
703/15/11663
803/14/11772
903/13/11884
1003/12/11914
1103/11/11691
1203/12/11560
Sheet1
Excel 2007
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Using the value in B1, a formula to count the number of rows for the other 9 values to appear. ex

For the B column B1=0 the remaining 9 values are 1,2,3,4,5,6,7,8,9. The number of rows it took for all 12 values to appear was 10. This is an example, not very many times will all 10 values appear in 10 rows.




CARBOB said:
The values in columns B:D range from 0-9. Need a formula that will count the number of rows that it takes for all 10 values to appear, in same column.
Excel Workbook
ABCDEFG
103/21/11005101112
203/20/11114
303/19/11226
403/18/11337
503/17/11449
603/16/11558
703/15/11663
803/14/11772
903/13/11884
1003/12/11914
1103/11/11691
1203/12/11560
Sheet1
Excel 2007
Click to expand...
 
Upvote 0
Does this return the desired result in all cases?

E1, copied across:

=MATCH(TRUE,MMULT((COUNTIF(OFFSET(B$1:B$20,,,ROW(B$1:B$20)-ROW(B$1)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
 
Upvote 0
Domenic said:
Does this return the desired result in all cases?

E1, copied across:

=MATCH(TRUE,MMULT((COUNTIF(OFFSET(B$1:B$20,,,ROW(B$1:B$20)-ROW(B$1)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
Click to expand...

I changed the ranges, the count works!! Thank you!!!!
Excel Workbook
ABCDE
103/21/1159527
203/20/1125926
303/19/1138625
403/18/1149424
503/17/1131923
603/16/1157322
703/15/1195521
803/14/1102720
903/13/1179419
1003/12/1101418
1103/11/1166117
1203/10/1113716
1303/09/1197838
1403/08/11498
1503/07/11038
1603/06/11271
1703/05/11662
1803/04/11792
1903/03/11632
2003/02/11711
2103/01/11499
2202/28/11940
2302/27/11364
2402/26/11352
2502/25/11542
2602/24/11681
2702/23/11865
Sheet1
Excel 2007
Cell Formulas
RangeFormula
E1=MATCH(TRUE,MMULT((COUNTIF(OFFSET(B1:B$1038,,,ROW(B1:B$1038)-ROW(B1)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
E2=MATCH(TRUE,MMULT((COUNTIF(OFFSET(B2:B$1038,,,ROW(B2:B$1038)-ROW(B2)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
E3=MATCH(TRUE,MMULT((COUNTIF(OFFSET(B3:B$1038,,,ROW(B3:B$1038)-ROW(B3)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
E4=MATCH(TRUE,MMULT((COUNTIF(OFFSET(B4:B$1038,,,ROW(B4:B$1038)-ROW(B4)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
E5=MATCH(TRUE,MMULT((COUNTIF(OFFSET(B5:B$1038,,,ROW(B5:B$1038)-ROW(B5)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
E6=MATCH(TRUE,MMULT((COUNTIF(OFFSET(B6:B$1038,,,ROW(B6:B$1038)-ROW(B6)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
E7=MATCH(TRUE,MMULT((COUNTIF(OFFSET(B7:B$1038,,,ROW(B7:B$1038)-ROW(B7)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
E8=MATCH(TRUE,MMULT((COUNTIF(OFFSET(B8:B$1038,,,ROW(B8:B$1038)-ROW(B8)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
E9=MATCH(TRUE,MMULT((COUNTIF(OFFSET(B9:B$1038,,,ROW(B9:B$1038)-ROW(B9)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
E10=MATCH(TRUE,MMULT((COUNTIF(OFFSET(B10:B$1038,,,ROW(B10:B$1038)-ROW(B10)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
E11=MATCH(TRUE,MMULT((COUNTIF(OFFSET(B11:B$1038,,,ROW(B11:B$1038)-ROW(B11)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
E12=MATCH(TRUE,MMULT((COUNTIF(OFFSET(B12:B$1038,,,ROW(B12:B$1038)-ROW(B12)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
E13=MATCH(TRUE,MMULT((COUNTIF(OFFSET(B13:B$1038,,,ROW(B13:B$1038)-ROW(B13)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
 
Upvote 0
I may have found a bug, if I counted right. The value for G1016 should be 16.
Excel Workbook
ABCDEFG
101606/21/08285285897###
101706/20/083513511007###
101806/19/087867861008
101906/18/087127121009
102006/17/089249241010
102106/16/084584581011
102206/15/089629621012
102306/14/08146146232
102406/13/08154154655
102506/12/085415411015
102606/11/080250251016
102706/10/08695695183
102806/09/080860861018
102906/08/08676676667
103006/07/08994994250
103106/06/088928921021
103206/05/080550551022
103306/04/08719719158
103406/03/08433433822
103506/02/088448441025
103606/01/081941941026
103705/31/08952952645
103805/30/08910910463
103905/29/0849849816
104005/28/08779779352
104105/27/080410411031
104205/26/08252252970
SKIPSUMS
Excel 2007
Cell Formulas
RangeFormula
G1016=MATCH(TRUE,MMULT((COUNTIF(OFFSET(D1016:D$1049,,,ROW(D1016:D$1049)-ROW(D1016)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
G1017=MATCH(TRUE,MMULT((COUNTIF(OFFSET(D1017:D$1049,,,ROW(D1017:D$1049)-ROW(D1017)+1),{0,1,2,3,4,5,6,7,8,9})>0)+0,{1;1;1;1;1;1;1;1;1;1})=10,0)
E1016=B1016&C1016&D1016
E1017=B1017&C1017&D1017
E1018=B1018&C1018&D1018
E1019=B1019&C1019&D1019
E1020=B1020&C1020&D1020
E1021=B1021&C1021&D1021
E1022=B1022&C1022&D1022
E1023=B1023&C1023&D1023
E1024=B1024&C1024&D1024
E1025=B1025&C1025&D1025
E1026=B1026&C1026&D1026
E1027=B1027&C1027&D1027
E1028=B1028&C1028&D1028
E1029=B1029&C1029&D1029
E1030=B1030&C1030&D1030
E1031=B1031&C1031&D1031
E1032=B1032&C1032&D1032
E1033=B1033&C1033&D1033
E1034=B1034&C1034&D1034
E1035=B1035&C1035&D1035
E1036=B1036&C1036&D1036
E1037=B1037&C1037&D1037
E1038=B1038&C1038&D1038
E1039=B1039&C1039&D1039
E1040=B1040&C1040&D1040
E1041=B1041&C1041&D1041
E1042=B1042&C1042&D1042
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 
Upvote 0
How can it be 16 when the numbers 3, 7, 9, and 0 do not occur within those rows? Also, the number 7 doesn't occur at all, hence #N/A.
 
Upvote 0
Never mind Domenic, I was working with the values in Column B and the formula is using the values in Column D, sorry.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
247
admvlad
A
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
119
Peter_SSs
Peter_SSs
K
  • Solved
Index with Multiple Conditions
Replies
2
Views
109
kivikatz
K
S
  • Solved
Individual Ranking
2
Replies
11
Views
469
sksanjeev786
S
G
  • Question Question
Format Pulled Data from Workbook into a Text Box
Replies
6
Views
125
Fuji
F

Forum statistics

Threads
1,224,597
Messages
6,179,808
Members
452,944
Latest member
2558216095

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top