COUNTIF and VLOOKUP

M

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

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.
 
M

mpenkala

Perfect - Thanks alot!

Matt

T. Valko said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top