sumproduct?

J

Jack Sons

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
 
B

Bob Phillips

=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)
 
J

Jack Sons

Dave,

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

Jack.
 
J

Jim May

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
 
J

Jack Sons

Bob,

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

Jack.
 
B

Bob Phillips

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 said:
Dave,

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

Jack.
Click to expand...
 
B

Bob Phillips

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)
 
B

Bob Phillips

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)
 
B

Bob Phillips

don't think so.

--
HTH

Bob

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

Jim May

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

bj

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

Jack Sons

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

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

Bob Phillips

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)
 
Top