Array functions ARGHH!
 

I'm pretty sure I need an array function for this, but nothing I do
works.

My data is basically two colums:

A B
0 0
0 2
1 0
1 0
1 4
2 1
3 3
3 3
4 0

etc

Now, I need to create a table that counts how many instance of each pair
there are. Like so:

0 1 2 3 4
___________
0 |1 0 1 0 0
1 |2 0 0 0 1
2 |0 1 0 0 0
3 |0 0 0 2 0
4 |1 0 0 0 0


So the upper left cell counts how many times there is a 0 in column A
AND a 0 in colum B (once). The next cell to the right counts how many
times there is a 0 in column A and a 1 in colum B (zero times). Etc.

The closest that I have come is

{=SUM(IF(($A$1:$A$10=0)+($B$1:$B$10=0),1,0))}

I actually have reference cells for the "0" that's being compared to, so
that I can use the same formula for every cell in the table, but I know
how to do that part.

The problem with this formula is that it counts how many times there is
a zero in column A OR a zero in column B.

HELP!

--
Marc.

After copying your data into B29:C37 (and A & B labels into B28 & C28), I was
able to use this formula in a 2 way data table & produce the same results as
in your post

Note that the 'A' value went into B27 - the cell above the 'A' label - and
the 'B' value went into C27

=SUMPRODUCT(--(B29:B37=B27)*--(C29:C37=C27))



"Marc Fleury" wrote:

I'm pretty sure I need an array function for this, but nothing I do
works.

My data is basically two colums:

A B
0 0
0 2
1 0
1 0
1 4
2 1
3 3
3 3
4 0

etc

Now, I need to create a table that counts how many instance of each pair
there are. Like so:

0 1 2 3 4
___________
0 |1 0 1 0 0
1 |2 0 0 0 1
2 |0 1 0 0 0
3 |0 0 0 2 0
4 |1 0 0 0 0


So the upper left cell counts how many times there is a 0 in column A
AND a 0 in colum B (once). The next cell to the right counts how many
times there is a 0 in column A and a 1 in colum B (zero times). Etc.

The closest that I have come is

{=SUM(IF(($A$1:$A$10=0)+($B$1:$B$10=0),1,0))}

I actually have reference cells for the "0" that's being compared to, so
that I can use the same formula for every cell in the table, but I know
how to do that part.

The problem with this formula is that it counts how many times there is
a zero in column A OR a zero in column B.

HELP!

--
Marc.