Well, if I didn;t see it myself I'd never beleive it, but closing and
reopening Excel did the trick. I cannot explain why I had trouble earlier,
but at least it's nice to know that my solution was tenable.

"Wazooli" wrote:

You know - I have been around Windows long enough to know that sometimes a
good swift restart is all things take. I am going to try that. Maybe some
other workbook I have open is "confusing" Excel. And I have checked 3 times
to make sure everything is in numeric formatting. No conditional formatting
exists, so I've no idea. Like I said earlier, I like these problems because
they chellenge my Excel skills. Ultimately, however, I will never have the
need to do a manipulation like the OP.



"Bob Phillips" wrote:

You're lucky I didn't call you Wazzer :-)

My A part resolves to

{1;1;0;0;0;0;0;0;1;1;1;1;1;1;1;1;1;1;1;1}

I have no idea what you have done, are you sure they are not text imn column
A?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wazooli" wrote in message
...
Lol - haven't been called Waz since college. Anyway, why do the first
resolve to 0;0;0;...? This seems to me to be a mistake. If the query is
A=1, and B=0, there are 3 in the original data posted. Shouldn't the
first

expression resolve to 0;0;1;1;1;0;0;0;0;...?

"Bob Phillips" wrote:

Waz,

I have replicated what you did and it still works for me. I get lots of
0 0
entries, because A10-A20 all resolve to 0 0 (that is why my formula
tested
for them), but it works.

The B part resolves to

{TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE ;TRUE;TRUE;TRUE;TRUE;TRUE;
TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
for me, or
{1;0;1;1;0;0;0;0;1;1;1;1;1;1;1;1;1;1;1;1}
if I use the double unary. Thnis is in cell E2.
--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wazooli" wrote in message
...
I used A1:B20 because JulieD suggested those. I merely filled in to
complete
the ranges. If my table is D1:I6, with cell D1 being empty (the
numbering
starts beneath and to the right), and I enter
=SUMPRODUCT(--($A$1:$A$20=$D2),--($B$1:$B$20=E$1), filldown and then
to
the
right, I get all zeroes. When I look at how Excel evaluates the
formula,
I
can verify that the '--' is working, but the values for the first part
of
the
expression resolve to '0;0;0;0;...' The second part is correct.




"Bob Phillips" wrote:

It does!

I just followed Domenic's instructions and it worked perfectly for
me.

Where does A20 and B20 com e into it in your reply?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wazooli" wrote in message
...
Doesn't. When I look at how Excel evaluates the formula, it seems
to
have
a
problem with $A$20 representing the first range. The sumproduct
function
gives all zeroes. The range $B$20, on the other hand, presents no
problem.

wazooli


"Domenic" wrote:

Assumptions:

1) Sheet1 contains your source table

2) the first row in Sheet2 contains your numbers (0 through 4)
starting
at B1

3) the first column in Sheet2 contains your other set of numbers
(0
through 4) starting at A2

Formula:

Sheet2!B2, copied across and down:

=SUMPRODUCT(--(Sheet1!$A$1:$A$9=$A2),--(Sheet1!$B$1:$B$9=B$1))

Hope this helps!

In article
34,
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.