sumproduct?

  • Thread starter Thread starter Jack Sons
  • Start date Start date
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
 
=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)
 
Dave,

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

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

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

Jack.
 
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...
 
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)
 
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)
 
don't think so.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Wow,, Once again proving the miracle of mathmatics/logic;
Thanks for explaining so well << makes all the difference..
Jim
 
I like the sign method, an alternate but similar way if blanks are truely
blank or ""
=sumproduct(sign(len(BB2:BB85&BC2:BC85)))
 
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.
 
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.
 
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)
 
Back
Top