=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