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