Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same row)
can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC cell
in the same row either one or both contain an "x". I know it is 19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number of x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me rather
inefficient. Although I did not drink alcohol, I can't find the correct
formula.

What did I wrong?

Jack Sons
The Netherlands

=SUMPRODUCT(--((bb2:bb85="X")+(Bc2:Bc85="x")0))



Jack Sons wrote:

Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same row)
can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC cell
in the same row either one or both contain an "x". I know it is 19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number of x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me rather
inefficient. Although I did not drink alcohol, I can't find the correct
formula.

What did I wrong?

Jack Sons
The Netherlands

--

Dave Peterson

Dave,

Thanks, but why the 0 ? And why not also (bb2:bb85="x")0 ?
Please explain.
TIA

Jack.

"Dave Peterson" schreef in bericht
...
=SUMPRODUCT(--((bb2:bb85="X")+(Bc2:Bc85="x")0))



Jack Sons wrote:

Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same row)
can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC
cell
in the same row either one or both contain an "x". I know it is 19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number of
x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me rather
inefficient. Although I did not drink alcohol, I can't find the correct
formula.

What did I wrong?

Jack Sons
The Netherlands

--

Dave Peterson

Because when both are x the + will give 2, when either is x, it will give 1,
when neither is x, it will give 0. So then test for 0 and you get a count
of either or.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jack Sons" wrote in message
...
Dave,

Thanks, but why the 0 ? And why not also (bb2:bb85="x")0 ?
Please explain.
TIA

Jack.

"Dave Peterson" schreef in bericht
...
=SUMPRODUCT(--((bb2:bb85="X")+(Bc2:Bc85="x")0))



Jack Sons wrote:

Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same
row)
can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC
cell
in the same row either one or both contain an "x". I know it is 19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number
of x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me
rather
inefficient. Although I did not drink alcohol, I can't find the correct
formula.

What did I wrong?

Jack Sons
The Netherlands

--

Dave Peterson

Just to add to Bob's reply...

This may have been more clear:

=SUMPRODUCT(--(((bb2:bb85="X")+(Bc2:Bc85="x"))0))

So excel evaluates the addition and then compares the sum of those with 0.



Jack Sons wrote:

Dave,

Thanks, but why the 0 ? And why not also (bb2:bb85="x")0 ?
Please explain.
TIA

Jack.

"Dave Peterson" schreef in bericht
...
=SUMPRODUCT(--((bb2:bb85="X")+(Bc2:Bc85="x")0))



Jack Sons wrote:

Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same row)
can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC
cell
in the same row either one or both contain an "x". I know it is 19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number of
x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me rather
inefficient. Although I did not drink alcohol, I can't find the correct
formula.

What did I wrong?

Jack Sons
The Netherlands

--

Dave Peterson

--

Dave Peterson

=SUMPRODUCT(SIGN((BB2:BB85="x")+((BC2:BC85="x"))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jack Sons" wrote in message
...
Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same row)
can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC
cell in the same row either one or both contain an "x". I know it is 19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number of
x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me rather
inefficient. Although I did not drink alcohol, I can't find the correct
formula.

What did I wrong?

Jack Sons
The Netherlands

Sorry Bob, but I've got to ask///
Going to the Sign function help (Fx) I
understand that it returns either a 1, 0, -1;
I can't incorporate this into the sumproduct
you have provided. Can you offer help?
Jim

"Bob Phillips" wrote:

=SUMPRODUCT(SIGN((BB2:BB85="x")+((BC2:BC85="x"))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jack Sons" wrote in message
...
Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same row)
can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC
cell in the same row either one or both contain an "x". I know it is 19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number of
x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me rather
inefficient. Although I did not drink alcohol, I can't find the correct
formula.

What did I wrong?

Jack Sons
The Netherlands

Jim,

The condition test ((BB2:BB85="x")+(BC2:BC85="x")) will either return 2; if
both are x, 1; if either are x; or 0 if neither are x. So SIGN(2) returns 1,
SIGN(1), returns 1, SIGN()) returns 0. Thus both x in both columns or x in
either colums can be forced to return 1, so this will count all instances
where x is in either or both columns.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jim May" wrote in message
...
Sorry Bob, but I've got to ask///
Going to the Sign function help (Fx) I
understand that it returns either a 1, 0, -1;
I can't incorporate this into the sumproduct
you have provided. Can you offer help?
Jim

"Bob Phillips" wrote:

=SUMPRODUCT(SIGN((BB2:BB85="x")+((BC2:BC85="x"))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells
contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same
row)
can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC
cell in the same row either one or both contain an "x". I know it is
19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number
of
x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me
rather
inefficient. Although I did not drink alcohol, I can't find the correct
formula.

What did I wrong?

Jack Sons
The Netherlands

Wow,, Once again proving the miracle of mathmatics/logic;
Thanks for explaining so well << makes all the difference..
Jim


"Bob Phillips" wrote:

Jim,

The condition test ((BB2:BB85="x")+(BC2:BC85="x")) will either return 2; if
both are x, 1; if either are x; or 0 if neither are x. So SIGN(2) returns 1,
SIGN(1), returns 1, SIGN()) returns 0. Thus both x in both columns or x in
either colums can be forced to return 1, so this will count all instances
where x is in either or both columns.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jim May" wrote in message
...
Sorry Bob, but I've got to ask///
Going to the Sign function help (Fx) I
understand that it returns either a 1, 0, -1;
I can't incorporate this into the sumproduct
you have provided. Can you offer help?
Jim

"Bob Phillips" wrote:

=SUMPRODUCT(SIGN((BB2:BB85="x")+((BC2:BC85="x"))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells
contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same
row)
can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC
cell in the same row either one or both contain an "x". I know it is
19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number
of
x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me
rather
inefficient. Although I did not drink alcohol, I can't find the correct
formula.

What did I wrong?

Jack Sons
The Netherlands

Bob,

Thanks, but why the SIGN ? Why will (--(BB2:BB85="x")
nd/or --(BC2:BC85="x") not work?
Please explain.
TIA

Jack.



"Bob Phillips" schreef in bericht
...
=SUMPRODUCT(SIGN((BB2:BB85="x")+((BC2:BC85="x"))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same row)
can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC
cell in the same row either one or both contain an "x". I know it is 19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number of
x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me rather
inefficient. Although I did not drink alcohol, I can't find the correct
formula.

What did I wrong?

Jack Sons
The Netherlands

See my reply to Jim.

The problem with the other way is that it double counts if both columns are
x, as you found. The SIGN, and Dave's 0, are there to eliminate that
double-counting.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jack Sons" wrote in message
...
Bob,

Thanks, but why the SIGN ? Why will (--(BB2:BB85="x")
d/or --(BC2:BC85="x") not work?
Please explain.
TIA

Jack.



"Bob Phillips" schreef in bericht
...
=SUMPRODUCT(SIGN((BB2:BB85="x")+((BC2:BC85="x"))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same
row) can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC
cell in the same row either one or both contain an "x". I know it is 19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number
of x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me
rather inefficient. Although I did not drink alcohol, I can't find the
correct formula.

What did I wrong?

Jack Sons
The Netherlands

I like the sign method, an alternate but similar way if blanks are truely
blank or ""
=sumproduct(sign(len(BB2:BB85&BC2:BC85)))

"Bob Phillips" wrote:

=SUMPRODUCT(SIGN((BB2:BB85="x")+((BC2:BC85="x"))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jack Sons" wrote in message
...
Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same row)
can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC
cell in the same row either one or both contain an "x". I know it is 19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number of
x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me rather
inefficient. Although I did not drink alcohol, I can't find the correct
formula.

What did I wrong?

Jack Sons
The Netherlands

try
=sumproduct(--or(BB2:BB85="x",BC2:BC85="x"))

"Jack Sons" wrote:

Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same row)
can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC cell
in the same row either one or both contain an "x". I know it is 19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number of x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me rather
inefficient. Although I did not drink alcohol, I can't find the correct
formula.

What did I wrong?

Jack Sons
The Netherlands

don't think so.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"bj" wrote in message
...
try
=sumproduct(--or(BB2:BB85="x",BC2:BC85="x"))

"Jack Sons" wrote:

Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same row)
can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC
cell
in the same row either one or both contain an "x". I know it is 19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number of
x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me rather
inefficient. Although I did not drink alcohol, I can't find the correct
formula.

What did I wrong?

Jack Sons
The Netherlands

thanks for catching that. You are right, it doesn't work.

"Bob Phillips" wrote:

don't think so.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"bj" wrote in message
...
try
=sumproduct(--or(BB2:BB85="x",BC2:BC85="x"))

"Jack Sons" wrote:

Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same row)
can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC
cell
in the same row either one or both contain an "x". I know it is 19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number of
x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me rather
inefficient. Although I did not drink alcohol, I can't find the correct
formula.

What did I wrong?

Jack Sons
The Netherlands

Bob,

That's a pity, because I was looking for an or function. That's why I was
working with the + in
SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x)))

By using the magical sumproduct I forgot that it really sums.

In fact I wanted to "count occurrences", should I use the countif function
with array entering? Is so, please be so kind to show me how and why.

I thank you in advance.

BTW, thanks also to all others who helped me, it really enlightened me.

Jack.

"Bob Phillips" schreef in bericht
...
don't think so.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"bj" wrote in message
...
try
=sumproduct(--or(BB2:BB85="x",BC2:BC85="x"))

"Jack Sons" wrote:

Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same
row)
can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC
cell
in the same row either one or both contain an "x". I know it is 19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number
of x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me
rather
inefficient. Although I did not drink alcohol, I can't find the correct
formula.

What did I wrong?

Jack Sons
The Netherlands

But Jack, Dave Peterson and I have both given you a SUMPRODUCT solution,
both using + as an OR operator.. The 'don't think so' remark referred to
bj's solution, which didn't work.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jack Sons" wrote in message
...
Bob,

That's a pity, because I was looking for an or function. That's why I was
working with the + in
SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x)))

By using the magical sumproduct I forgot that it really sums.

In fact I wanted to "count occurrences", should I use the countif function
with array entering? Is so, please be so kind to show me how and why.

I thank you in advance.

BTW, thanks also to all others who helped me, it really enlightened me.

Jack.

"Bob Phillips" schreef in bericht
...
don't think so.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"bj" wrote in message
...
try
=sumproduct(--or(BB2:BB85="x",BC2:BC85="x"))

"Jack Sons" wrote:

Hi all,

In BB2:BB85 and BC2:BC85 some cells contain "x", all other cells
contain
nothing.
If a BB cell contains an "x" the corresponding BC cell (in the same
row)
can contain either "x" or nothing, vice versa.
I tried to find the number of cases in which of the BB cell and the BC
cell
in the same row either one or both contain an "x". I know it is 19.

But

=SUMPRODUCT(--(BB2:BB85="x")+(--(BC2:BC85="x))) gives 32 (total number
of x)
=SUMPRODUCT(--(BB2:BB85="x")*(--(BC2:BC85="x"))) gives 13 (number of
corresponding cells with both x)

so I have to subtract to find the correct number which seems to me
rather
inefficient. Although I did not drink alcohol, I can't find the correct
formula.

What did I wrong?

Jack Sons
The Netherlands