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




"Ome r" wrote:

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

One way:

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

In article ,
Omer wrote:

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

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

Hi

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

Thanks

"JE McGimpsey" wrote:

One way:

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

In article ,
Omer wrote:

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

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




"CmK" wrote:


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




"Ome r" wrote:

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

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)



"CmK" wrote in message
...
Hi

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

Thanks

"JE McGimpsey" wrote:

One way:

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

In article ,
Omer wrote:

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

=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)" wrote in
message ...
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

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

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

=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

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)

"Rick Rothstein (MVP - VB)" wrote in
message ...
=SUMPRODUCT((A1:A5="yes")+(B1:B5="yes")*(A1:A5<B1 :B5))

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

=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

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.

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