Count after given condition is met
 

Hi all

would like your help for the following

Need to count which and how many numbers appear after a given number

ie
After After After After After After
1 2 3 4 5 6
1
2 1 1
3 1 1 1 1
4 1 2 3 1 1
5 2 1
6 1

this was obtained from data on column c, that is displayed as follows:
3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

Tks in advance
Antonio

Count after given condition is met
 

Sumproduct will do this. Assuming that your data is is C1:c23 enter this
formula in Row 1 and copy down to row 6

=SUMPRODUCT(--($C$1:$C$23=ROW()))

If you want a header in row 1 and your summary to begin in row 2 use:

=SUMPRODUCT(--($C$1:$C$23=ROW()-1))

Regards
Peter

"Antonio" wrote:

Hi all

would like your help for the following

Need to count which and how many numbers appear after a given number

ie
After After After After After After
1 2 3 4 5 6
1
2 1 1
3 1 1 1 1
4 1 2 3 1 1
5 2 1
6 1

this was obtained from data on column c, that is displayed as follows:
3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

Tks in advance
Antonio

Count after given condition is met
 

Hi Billy

With the formula, I obtain the total quantity of numbers, but not
how many after occurence 1, 2, 3, 4, 5 or 6.

What M I doing wrong??

Tks for the help

"Billy Liddel" wrote:

Sumproduct will do this. Assuming that your data is is C1:c23 enter this
formula in Row 1 and copy down to row 6

=SUMPRODUCT(--($C$1:$C$23=ROW()))

If you want a header in row 1 and your summary to begin in row 2 use:

=SUMPRODUCT(--($C$1:$C$23=ROW()-1))

Regards
Peter

"Antonio" wrote:

Hi all

would like your help for the following

Need to count which and how many numbers appear after a given number

ie
After After After After After After
1 2 3 4 5 6
1
2 1 1
3 1 1 1 1
4 1 2 3 1 1
5 2 1
6 1

this was obtained from data on column c, that is displayed as follows:
3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

Tks in advance
Antonio

Count after given condition is met
 

I thought that the numbers went up to just six. Just copy the formula down to
include the highest number in the range.

Peter

"Antonio" wrote:

Hi Billy

With the formula, I obtain the total quantity of numbers, but not
how many after occurence 1, 2, 3, 4, 5 or 6.

What M I doing wrong??

Tks for the help

"Billy Liddel" wrote:

Sumproduct will do this. Assuming that your data is is C1:c23 enter this
formula in Row 1 and copy down to row 6

=SUMPRODUCT(--($C$1:$C$23=ROW()))

If you want a header in row 1 and your summary to begin in row 2 use:

=SUMPRODUCT(--($C$1:$C$23=ROW()-1))

Regards
Peter

"Antonio" wrote:

Hi all

would like your help for the following

Need to count which and how many numbers appear after a given number

ie
After After After After After After
1 2 3 4 5 6
1
2 1 1
3 1 1 1 1
4 1 2 3 1 1
5 2 1
6 1

this was obtained from data on column c, that is displayed as follows:
3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

Tks in advance
Antonio

Count after given condition is met
 

This is the result I obtain
1 2 3 4 5 6
0 0 0 0 0 0
4 30 30 30 30 30 30
2 9 9 9 9 9 9
2 8 8 8 8 8 8
3 5 5 5 5 5 5
2 0 0 0 0 0 0
2
4
3
2
4
3
2
4
2
4
4
2
4
4
2
5
3
2
2
3
5
2
2
2
2
3
2
2
2
2
2
2
2
2
2
2
5
2
5
2
3
2
3
5
2
2
3
















































































































































































































































"Billy Liddel" wrote:

I thought that the numbers went up to just six. Just copy the formula down to
include the highest number in the range.

Peter

"Antonio" wrote:

Hi Billy

With the formula, I obtain the total quantity of numbers, but not
how many after occurence 1, 2, 3, 4, 5 or 6.

What M I doing wrong??

Tks for the help

"Billy Liddel" wrote:

Sumproduct will do this. Assuming that your data is is C1:c23 enter this
formula in Row 1 and copy down to row 6

=SUMPRODUCT(--($C$1:$C$23=ROW()))

If you want a header in row 1 and your summary to begin in row 2 use:

=SUMPRODUCT(--($C$1:$C$23=ROW()-1))

Regards
Peter

"Antonio" wrote:

Hi all

would like your help for the following

Need to count which and how many numbers appear after a given number

ie
After After After After After After
1 2 3 4 5 6
1
2 1 1
3 1 1 1 1
4 1 2 3 1 1
5 2 1
6 1

this was obtained from data on column c, that is displayed as follows:
3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

Tks in advance
Antonio

Count after given condition is met
 

Billy

Uloaded one of the files

http://www.freefilehosting.net/download/3h501

Maybe U can see what I'm doing wrong

Tks in advance

"Billy Liddel" wrote:

I thought that the numbers went up to just six. Just copy the formula down to
include the highest number in the range.

Peter

"Antonio" wrote:

Hi Billy

With the formula, I obtain the total quantity of numbers, but not
how many after occurence 1, 2, 3, 4, 5 or 6.

What M I doing wrong??

Tks for the help

"Billy Liddel" wrote:

Sumproduct will do this. Assuming that your data is is C1:c23 enter this
formula in Row 1 and copy down to row 6

=SUMPRODUCT(--($C$1:$C$23=ROW()))

If you want a header in row 1 and your summary to begin in row 2 use:

=SUMPRODUCT(--($C$1:$C$23=ROW()-1))

Regards
Peter

"Antonio" wrote:

Hi all

would like your help for the following

Need to count which and how many numbers appear after a given number

ie
After After After After After After
1 2 3 4 5 6
1
2 1 1
3 1 1 1 1
4 1 2 3 1 1
5 2 1
6 1

this was obtained from data on column c, that is displayed as follows:
3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

Tks in advance
Antonio

Count after given condition is met
 

Antonio

What you need is more suited to a macro. Assuming that your spreadsheet is
the same as the uploaded file then the following will work for you. It does
return different, but correct results from your data.

Sub test()
Dim LastRow As Long
Dim MyTable As Range, MyList As Range
Dim d, f
Range("C1").Activate
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
Set MyTable = Range("F2:K7")
Set MyList = Range(Cells(2, 3), Cells(LastRow, 3))

MyTable = 0
For i = 2 To LastRow - 1
d = Cells(i, 3)
f = Cells(i + 1, 3)
Select Case f
Case 1
If d = 1 Then Cells(2, 6) = Cells(2, 6) + 1
If d = 2 Then Cells(2, 7) = Cells(2, 7) + 1
If d = 3 Then Cells(2, 8) = Cells(2, 8) + 1
If d = 4 Then Cells(2, 9) = Cells(2, 9) + 1
If d = 5 Then Cells(2, 10) = Cells(2, 10) + 1
If d = 6 Then Cells(2, 11) = Cells(2, 11) + 1
Case 2
If d = 1 Then Cells(3, 6) = Cells(3, 6) + 1
If d = 2 Then Cells(3, 7) = Cells(3, 7) + 1
If d = 3 Then Cells(3, 8) = Cells(3, 8) + 1
If d = 4 Then Cells(3, 9) = Cells(3, 9) + 1
If d = 5 Then Cells(3, 10) = Cells(3, 10) + 1
If d = 6 Then Cells(3, 11) = Cells(3, 11) + 1
Case 3
If d = 1 Then Cells(4, 6) = Cells(4, 6) + 1
If d = 2 Then Cells(4, 7) = Cells(4, 7) + 1
If d = 3 Then Cells(4, 8) = Cells(4, 8) + 1
If d = 4 Then Cells(4, 9) = Cells(4, 9) + 1
If d = 5 Then Cells(4, 10) = Cells(4, 10) + 1
If d = 6 Then Cells(4, 11) = Cells(4, 11) + 1
Case 4
If d = 1 Then Cells(5, 6) = Cells(5, 6) + 1
If d = 2 Then Cells(5, 7) = Cells(5, 7) + 1
If d = 3 Then Cells(5, 8) = Cells(5, 8) + 1
If d = 4 Then Cells(5, 9) = Cells(5, 9) + 1
If d = 5 Then Cells(5, 10) = Cells(5, 10) + 1
If d = 6 Then Cells(5, 11) = Cells(5, 11) + 1
Case 5
If d = 1 Then Cells(6, 6) = Cells(6, 6) + 1
If d = 2 Then Cells(6, 7) = Cells(6, 7) + 1
If d = 3 Then Cells(6, 8) = Cells(6, 8) + 1
If d = 4 Then Cells(6, 9) = Cells(6, 9) + 1
If d = 5 Then Cells(6, 10) = Cells(6, 10) + 1
If d = 6 Then Cells(6, 11) = Cells(6, 11) + 1
Case 6
If d = 1 Then Cells(7, 6) = Cells(7, 6) + 1
If d = 2 Then Cells(7, 7) = Cells(7, 7) + 1
If d = 3 Then Cells(7, 8) = Cells(7, 8) + 1
If d = 4 Then Cells(7, 9) = Cells(7, 9) + 1
If d = 5 Then Cells(7, 10) = Cells(7, 10) + 1
If d = 6 Then Cells(7, 11) = Cells(7, 11) + 1
End Select
Next
End Sub


Press Alt + F11, Insert module then paste the code. close and return to
file. make sure that you are in the right sheet and run the code.

Regards
Peter

Ps sorry for the delay, I share the computer and my ISP was down this morning.

"Antonio" wrote:

Billy

Uloaded one of the files

http://www.freefilehosting.net/download/3h501

Maybe U can see what I'm doing wrong

Tks in advance

"Billy Liddel" wrote:

I thought that the numbers went up to just six. Just copy the formula down to
include the highest number in the range.

Peter

"Antonio" wrote:

Hi Billy

With the formula, I obtain the total quantity of numbers, but not
how many after occurence 1, 2, 3, 4, 5 or 6.

What M I doing wrong??

Tks for the help

"Billy Liddel" wrote:

Sumproduct will do this. Assuming that your data is is C1:c23 enter this
formula in Row 1 and copy down to row 6

=SUMPRODUCT(--($C$1:$C$23=ROW()))

If you want a header in row 1 and your summary to begin in row 2 use:

=SUMPRODUCT(--($C$1:$C$23=ROW()-1))

Regards
Peter

"Antonio" wrote:

Hi all

would like your help for the following

Need to count which and how many numbers appear after a given number

ie
After After After After After After
1 2 3 4 5 6
1
2 1 1
3 1 1 1 1
4 1 2 3 1 1
5 2 1
6 1

this was obtained from data on column c, that is displayed as follows:
3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

Tks in advance
Antonio

Count after given condition is met
 

Billy, sorry for the late reply

The macro works like a charm, but increased one column and used a if
statement, and then a count value on that column

Tks for your kind assistance

rgds
Antonio

"Billy Liddel" wrote:

Antonio

What you need is more suited to a macro. Assuming that your spreadsheet is
the same as the uploaded file then the following will work for you. It does
return different, but correct results from your data.

Sub test()
Dim LastRow As Long
Dim MyTable As Range, MyList As Range
Dim d, f
Range("C1").Activate
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
Set MyTable = Range("F2:K7")
Set MyList = Range(Cells(2, 3), Cells(LastRow, 3))

MyTable = 0
For i = 2 To LastRow - 1
d = Cells(i, 3)
f = Cells(i + 1, 3)
Select Case f
Case 1
If d = 1 Then Cells(2, 6) = Cells(2, 6) + 1
If d = 2 Then Cells(2, 7) = Cells(2, 7) + 1
If d = 3 Then Cells(2, 8) = Cells(2, 8) + 1
If d = 4 Then Cells(2, 9) = Cells(2, 9) + 1
If d = 5 Then Cells(2, 10) = Cells(2, 10) + 1
If d = 6 Then Cells(2, 11) = Cells(2, 11) + 1
Case 2
If d = 1 Then Cells(3, 6) = Cells(3, 6) + 1
If d = 2 Then Cells(3, 7) = Cells(3, 7) + 1
If d = 3 Then Cells(3, 8) = Cells(3, 8) + 1
If d = 4 Then Cells(3, 9) = Cells(3, 9) + 1
If d = 5 Then Cells(3, 10) = Cells(3, 10) + 1
If d = 6 Then Cells(3, 11) = Cells(3, 11) + 1
Case 3
If d = 1 Then Cells(4, 6) = Cells(4, 6) + 1
If d = 2 Then Cells(4, 7) = Cells(4, 7) + 1
If d = 3 Then Cells(4, 8) = Cells(4, 8) + 1
If d = 4 Then Cells(4, 9) = Cells(4, 9) + 1
If d = 5 Then Cells(4, 10) = Cells(4, 10) + 1
If d = 6 Then Cells(4, 11) = Cells(4, 11) + 1
Case 4
If d = 1 Then Cells(5, 6) = Cells(5, 6) + 1
If d = 2 Then Cells(5, 7) = Cells(5, 7) + 1
If d = 3 Then Cells(5, 8) = Cells(5, 8) + 1
If d = 4 Then Cells(5, 9) = Cells(5, 9) + 1
If d = 5 Then Cells(5, 10) = Cells(5, 10) + 1
If d = 6 Then Cells(5, 11) = Cells(5, 11) + 1
Case 5
If d = 1 Then Cells(6, 6) = Cells(6, 6) + 1
If d = 2 Then Cells(6, 7) = Cells(6, 7) + 1
If d = 3 Then Cells(6, 8) = Cells(6, 8) + 1
If d = 4 Then Cells(6, 9) = Cells(6, 9) + 1
If d = 5 Then Cells(6, 10) = Cells(6, 10) + 1
If d = 6 Then Cells(6, 11) = Cells(6, 11) + 1
Case 6
If d = 1 Then Cells(7, 6) = Cells(7, 6) + 1
If d = 2 Then Cells(7, 7) = Cells(7, 7) + 1
If d = 3 Then Cells(7, 8) = Cells(7, 8) + 1
If d = 4 Then Cells(7, 9) = Cells(7, 9) + 1
If d = 5 Then Cells(7, 10) = Cells(7, 10) + 1
If d = 6 Then Cells(7, 11) = Cells(7, 11) + 1
End Select
Next
End Sub


Press Alt + F11, Insert module then paste the code. close and return to
file. make sure that you are in the right sheet and run the code.

Regards
Peter

Ps sorry for the delay, I share the computer and my ISP was down this morning.

"Antonio" wrote:

Billy

Uloaded one of the files

http://www.freefilehosting.net/download/3h501

Maybe U can see what I'm doing wrong

Tks in advance

"Billy Liddel" wrote:

I thought that the numbers went up to just six. Just copy the formula down to
include the highest number in the range.

Peter

"Antonio" wrote:

Hi Billy

With the formula, I obtain the total quantity of numbers, but not
how many after occurence 1, 2, 3, 4, 5 or 6.

What M I doing wrong??

Tks for the help

"Billy Liddel" wrote:

Sumproduct will do this. Assuming that your data is is C1:c23 enter this
formula in Row 1 and copy down to row 6

=SUMPRODUCT(--($C$1:$C$23=ROW()))

If you want a header in row 1 and your summary to begin in row 2 use:

=SUMPRODUCT(--($C$1:$C$23=ROW()-1))

Regards
Peter

"Antonio" wrote:

Hi all

would like your help for the following

Need to count which and how many numbers appear after a given number

ie
After After After After After After
1 2 3 4 5 6
1
2 1 1
3 1 1 1 1
4 1 2 3 1 1
5 2 1
6 1

this was obtained from data on column c, that is displayed as follows:
3
5
4
5
3
4
4
2
4
3
4
4
3
5
6
4
4
3
3
4
2
3
2

Tks in advance
Antonio