COUNTIF and VLOOKUP

[mpenkala]

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

 

[T. Valko]

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.

 

[mpenkala]

Perfect - Thanks alot!

Matt

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.

 

[T. Valko]

You're welcome. Thanks for the feedback!