sumproduct

[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

 

[Guest]

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

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--((A1:A5="yes")+(B1:B5="yes")>0))

 

[Rick Rothstein \(MVP - VB\)]

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

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

 

[Guest]

Hi

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

Thanks

 

[Guest]

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

 

[Peo Sjoblom]

Really, how would the IF function return 5 from that?


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Bob Phillips]

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

 

[Rick Rothstein \(MVP - VB\)]

=SUMPRODUCT(--((($A$1:$A$5="Yes")+($B$1:$B$5="Yes"))>0))

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

 

[Bob Phillips]

Have you seen this page http://www.xldynamic.com/source/xld.SUMPRODUCT.html.

--
HTH

Bob

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

 

[Bernd P]

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

 

[Rick Rothstein \(MVP - VB\)]

Have you seen this page

http://www.xldynamic.com/source/xld.SUMPRODUCT.html.

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