SUMPRODUCT and OR operator

M

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
 
D

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
 
B

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

bplumhoff

Hello Madiya,

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

HTH,
Bernd
 
Top