Index and match frequency problem

I am trying to build a frequency table.

In the source, I have a column with a bin ID. I have 7 other columns with numbers. There are multiple instances of each bin, and multiple (unique) instances of each of the other 7 columns (Sectors).

Basically I would like to match each cell to its bin and count their frequency. My frequency table will be numbered 1 to the highest number given in a sector cell.

For example:

Bin	Sector 1	Sector 2	Sector 3	Sector 4	Sector 5	Sector 6	Sector 7
11	13	16	34	22	23	24	20
16	66	73	18	11	19	20	29
11	26	21	34	33	20	31	30

For the frequency table, Bin 11 for Sector 3 would indicate 2 under the number 34 because 34 shows up twice. However, as you will notice the number 20 is also in both 11 bins, it will only be listed under its sectors as appearing once, because it does not appear in the same Bin AND Sector more than once. Am I being clear? Each bin is unique and each instance of a sector is unique.

EDIT:

Perhaps one solution would be a combination of an IF, MATCH, and COUNTIF statement?

Like

Please Login or Register  to view this content.

Where you would drag that across and down, making substitutions.

This should work. Change the references to match your data.

Sounds to me like you need a 3-dimensional table for this, and it will be huge?

basically, lets say you have 20 bins and 7 sectors, and you want to ount how many times a specfic number appears in each of thos - thats already 140 variables, * how ever namny numbers you would have inside?

(unless I have totally misunderstood?)

hi there. say your 1st table is in A1:H4 & your 2nd table is in K1:N4, your formula would start in M2:
=COUNTIFS($A$2:$A$4,$K2,INDEX($B$2:$H$4,,$L2),M$1)