SUMPRODUCT and OR operator

[Madiya]

I need to count the no of cells that has Col A=yes and (Either Col G or
Col H )=yes.
This is somewhat different then using OR operator in the same
column(like Col G=red or blue should be counted). What I want is If
cell value in any of the col in same row is yes then it sould be
counted.
for e.g. if Row 15 has in
col G yes and col H nil - counted
col G nil and col H nil - not counted
col G yes and col H yes - counted
col G nil and col H yes - counted

I hope I have clearly explained my problem. How can I do it?

Regards,
Madiya

 

[Ardus Petus]

Try:
=SUMPRODUCT((A1:A5="yes")*((G1:G5="yes")+(H1:H5="Yes")>0))

HTH

 

[Dav]

Its not an elegant solution and I am sure there must be a shorter one,
but for expediency try

=SUMPRODUCT((A3:A7="Yes")*(G3:G7="Y"))+SUMPRODUCT((A3:A7="Yes")*(H3:H7="Y"))-SUMPRODUCT((A3:A7="Y")*(G3:G7="Y")*(H3:H7="Yes"))

Extend your ranges accordingly

Regards

Dav

 

[Bob Phillips]

Madiya,

=SUMPRODUCT(--(COUNTIF(OFFSET(A1,,ROW(INDIRECT("1:15")),10),"yes")))

change the 1:15 to refer to the rows you want to count, and the following
,10 to refer to the columns to check

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[bplumhoff]

Hello Madiya,

=SUMPRODUCT(--(A1:A99="Yes"),SIGN(--(G1:G99="Yes")--(H1:H99="Yes")))

HTH,
Bernd

 

[Don Guillett]

does this work?
=SUMPRODUCT((a2:a14="Y")*(g2:h14="Y"))