sumproduct

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need one help too :)

I want to find number of "yes" in both columns but if yes is in both columns
it should be counted as 1

for example
A B
yes no
no yes
yes no
no yes
yes yes

The answer should be 5
I tried the formula =SUMPRODUCT((A1:A5="Yes")*OR(B1:B5="YES")) but its not
giving me the right answer, its giving me 3. Can any body please tell me it
in Sumproduct form as I have to compile the resuolt with other columns too
 
Hi

Don t really get what your trying to say but what I can comprehend from this
here is my suggestion why dont you use the
IF function =if(a2="yes" and b2="yes",1,0)
Hope this helped
 
I want to find number of "yes" in both columns but if yes is in both
columns
it should be counted as 1

for example
A B
yes no
no yes
yes no
no yes
yes yes

The answer should be 5
I tried the formula =SUMPRODUCT((A1:A5="Yes")*OR(B1:B5="YES")) but its not
giving me the right answer, its giving me 3. Can any body please tell me
it
in Sumproduct form as I have to compile the resuolt with other columns too
Click to expand...

If you didn't put the last restriction on the question, I would have
answered you with this formula...

=COUNTIF(A1:B5,"=yes")-SUMPRODUCT((A1:A5="yes")*(B1:B5="yes"))

However, since you want it in SUMPRODUCT form only...

=SUMPRODUCT((A1:A5="yes")+(B1:B5="yes")*(A1:A5<>B1:B5))

although I am not sure that is the best form for it.

Rick
 
Hi

Just curious why would use sumproduct fucntion for this case when if
function seems appropriate

Thanks
 
Thanks for your early reply CMK, I am very sorry as I was unable to tell you
my exact purpose.

I want to do it in this way:

A B
yes no
no yes
yes no
no yes
yes yes

if "A" has "Yes" and "B" has "NO", it should be counted as 1
if "A" has "NO" and "B" has "Yes", it should be counted as 1
if "A" has "Yes" and "B" has "YES", it should be counted as 1

So, the answer at the end in this example is 5. Any reply would be highly
appreciated
 
=SUMPRODUCT(--((($A$1:$A$5="Yes")+($B$1:$B$5="Yes"))>0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
=SUMPRODUCT((A1:A5="yes")+(B1:B5="yes")*(A1:A5 said:
=SUMPRODUCT(--((($A$1:$A$5="Yes")+($B$1:$B$5="Yes"))>0))
Click to expand...

Ah, yes... I see... very good. This SUMPRODUCT construction is still
somewhat "new" to me, so I am struggling a little with visualizing it.
Slowly, but surely, it is coming along, though. Thank you for posting your
formula.... every example I see turns on another light bulb in the old brain
of mine.

Rick
 
Hello,

My suggested realisation of the OR within SUMPRODUCT is SIGN of the
sum of what should be or'ed:

=SUMPRODUCT(SIGN(($A$1:$A$5="Yes")+($B$1:$B$5="Yes")))

Regards,
Bernd
 
Have you seen this page
http://www.xldynamic.com/source/xld.SUMPRODUCT.html.
Click to expand...

No, I hadn't. Thanks for the link. I have doped out most of what is there on
my own. I tend to learn (retain for a longer time) things if I fight my way
through them using trial and error until what is going on finally reveals
itself. This is the method I used to learn the various computer languages I
met up with across the years (I am a totally self-taught programmer with no
formal classroom time in any of the languages I know). Not necessarily a
recommended learning method for others to follow, but it has served me well
across the years. I did find the examples helpful though... they tend to
give me situations that I might not come across (or think of) in the trial
and error approach I use. The key, for me, is to internalize the underlying
core concepts of whatever I am learning and, from them, build whatever
solutions the problem at hand requires. Thank you again for the link.

Rick
 
Back
Top