View Single Post
  #1   Report Post  
Marc Fleury
 
Posts: n/a
Default 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.