Count after given condition is met

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Antonio - 10 May 2008 18:46 GMT

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

Reply to this Message

Billy Liddel - 10 May 2008 20:11 GMT

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

> Hi all
>
[quoted text clipped - 39 lines]
> Tks in advance
> Antonio

Reply to this Message

Antonio - 10 May 2008 22:57 GMT

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

> Sumproduct will do this. Assuming that your data is is C1:c23 enter this
> formula in Row 1 and copy down to row 6
[quoted text clipped - 51 lines]
> > Tks in advance
> > Antonio

Reply to this Message

Billy Liddel - 11 May 2008 21:08 GMT

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

Peter

> Hi Billy
>
[quoted text clipped - 60 lines]
> > > Tks in advance
> > > Antonio

Reply to this Message

Antonio - 11 May 2008 22:02 GMT

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

> I thought that the numbers went up to just six. Just copy the formula down to
> include the highest number in the range.
[quoted text clipped - 65 lines]
> > > > Tks in advance
> > > > Antonio

Reply to this Message

Antonio - 11 May 2008 22:38 GMT

Billy

Uloaded one of the files

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

Maybe U can see what I'm doing wrong

Tks in advance

> I thought that the numbers went up to just six. Just copy the formula down to
> include the highest number in the range.
[quoted text clipped - 65 lines]
> > > > Tks in advance
> > > > Antonio

Reply to this Message

Billy Liddel - 13 May 2008 15:55 GMT

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.

> Billy
>
[quoted text clipped - 75 lines]
> > > > > Tks in advance
> > > > > Antonio

Reply to this Message

Antonio - 15 May 2008 19:43 GMT

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

> Antonio
>
[quoted text clipped - 149 lines]
> > > > > > Tks in advance
> > > > > > Antonio

Reply to this Message