COUNTIF and VLOOKUP
 

Hey,

I'm trying to set up the following. I have a list of about 100 numbers in
ColA and I want to be able to count how many times each number is followed by
a specific number.

My 100 numbers are all between 0 and 30. So if the list was something like:

14
0
12
12
4
30
0
12

I'd want my table to say that 14 was followed by 0 once, 0 was followed by
12 twice, 12 was followed by 12 once and 4 once, 4 was followed by 30 once,
30 was followed by 0 once. My table would be 31x31.

0-1-2-3-4-5-6-etc...
1
2
3
4
5
6
etc...

I figured a countif might do it, but I'm not sure how to incorperate the
entire 100 numbers.

Thanks,
Matt

COUNTIF and VLOOKUP
 

Try this:

Numbers in A1:A8

Matrix table column headers in D1:x1, row headers in C2:Cx

Enter this formula in D2:

=SUMPRODUCT(--($A$1:$A$7=D$1),--($A$2:$A$8=$C2))

Note how the references to column A are offset.

Copy across then down as needed.


--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
Hey,

I'm trying to set up the following. I have a list of about 100 numbers in
ColA and I want to be able to count how many times each number is followed
by
a specific number.

My 100 numbers are all between 0 and 30. So if the list was something
like:

14
0
12
12
4
30
0
12

I'd want my table to say that 14 was followed by 0 once, 0 was followed by
12 twice, 12 was followed by 12 once and 4 once, 4 was followed by 30
once,
30 was followed by 0 once. My table would be 31x31.

0-1-2-3-4-5-6-etc...
1
2
3
4
5
6
etc...

I figured a countif might do it, but I'm not sure how to incorperate the
entire 100 numbers.

Thanks,
Matt

COUNTIF and VLOOKUP
 

Perfect - Thanks alot!

Matt

"T. Valko" wrote:

Try this:

Numbers in A1:A8

Matrix table column headers in D1:x1, row headers in C2:Cx

Enter this formula in D2:

=SUMPRODUCT(--($A$1:$A$7=D$1),--($A$2:$A$8=$C2))

Note how the references to column A are offset.

Copy across then down as needed.


--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
Hey,

I'm trying to set up the following. I have a list of about 100 numbers in
ColA and I want to be able to count how many times each number is followed
by
a specific number.

My 100 numbers are all between 0 and 30. So if the list was something
like:

14
0
12
12
4
30
0
12

I'd want my table to say that 14 was followed by 0 once, 0 was followed by
12 twice, 12 was followed by 12 once and 4 once, 4 was followed by 30
once,
30 was followed by 0 once. My table would be 31x31.

0-1-2-3-4-5-6-etc...
1
2
3
4
5
6
etc...

I figured a countif might do it, but I'm not sure how to incorperate the
entire 100 numbers.

Thanks,
Matt

COUNTIF and VLOOKUP
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
Perfect - Thanks alot!

Matt

"T. Valko" wrote:

Try this:

Numbers in A1:A8

Matrix table column headers in D1:x1, row headers in C2:Cx

Enter this formula in D2:

=SUMPRODUCT(--($A$1:$A$7=D$1),--($A$2:$A$8=$C2))

Note how the references to column A are offset.

Copy across then down as needed.


--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
Hey,

I'm trying to set up the following. I have a list of about 100 numbers
in
ColA and I want to be able to count how many times each number is
followed
by
a specific number.

My 100 numbers are all between 0 and 30. So if the list was something
like:

14
0
12
12
4
30
0
12

I'd want my table to say that 14 was followed by 0 once, 0 was followed
by
12 twice, 12 was followed by 12 once and 4 once, 4 was followed by 30
once,
30 was followed by 0 once. My table would be 31x31.

0-1-2-3-4-5-6-etc...
1
2
3
4
5
6
etc...

I figured a countif might do it, but I'm not sure how to incorperate
the
entire 100 numbers.

Thanks,
Matt