Counting occurences

[Pepestru]

Hi Excel Professors,
I have tried for some time to use excel functions for the following simple problem:
I have a spreadsheet with two columns (a few thousand rows) having the numbers 1 to 20.
I needs a matrix ( 1- 20 horizontal and 1-20 vertical) showing the number of occurrences of each combination found in the rows.
So I want to know how many times the combination 1-2 occurs, and so forth for all combinations with of 1-20 to 20-20.
Is this possible using standard functions of Excel?
Thanks for your help.
Paul

 

[Aladin Akyurek]

Hi Excel Professors,
I have tried for some time to use excel functions for the following simple problem:
I have a spreadsheet with two columns (a few thousand rows) having the numbers 1 to 20.
I needs a matrix ( 1- 20 horizontal and 1-20 vertical) showing the number of occurrences of each combination found in the rows.
So I want to know how many times the combination 1-2 occurs, and so forth for all combinations with of 1-20 to 20-20.
Is this possible using standard functions of Excel?
Thanks for your help.
Paul

Book1
	A	B	C	D	E	F	G	H	I	J
1	X	Y			1	2	3	4	5
2	3	5		1	0	1	1	2	0
3	2	5		2	0	0	1	2	1
4	3	3		3	0	0	2	1	1
5	5	1		4	0	0	0	0	0
6	5	3		5	1	1	3	0	2
7	3	4
8	1	3
9	2	3
10	1	4		Count of X	Y
11	5	3		X	1	2	3	4	5
12	1	2		1		1	1	2
13	2	4		2			1	2	1
14	5	2		3			2	1	1
15	3	3		5	1	1	3		2
16	1	4
17	5	5
18	5	3
19	5	5
20	2	4
Sheet1

E2, copied across then down:

=SUMPRODUCT(--($A$2:$A$20=$D2),--($B$2:$B$20=E$1))

The same results can be achieved with a pivot table as shown in D10:I15 in the exhibit.

 

[Pepestru]

Many thanks. It does exactly what I need!
Although I never would think about using this function, now I still do not understand why this does the job.........
Never mind, and thanks again,
Paul

 

[Aladin Akyurek]

Many thanks. It does exactly what I need!

Thanks for feedback.

Although I never would think about using this function, now I still do not understand why this does the job.........
...

This entry might help:

http://www.mrexcel.com/wwwboard/messages/8961.html

along with:

http://www.mrexcel.com/board2/viewtopic.php?t=133602

 

[ken2step]

My solution was not as elegant!

Sweet!