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

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

HTH
--
AP

"Madiya" a écrit dans le message de news:
...
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

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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=559870

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)

"Madiya" wrote in message
ups.com...
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

Hello Madiya,

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

HTH,
Bernd

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

--
Don Guillett
SalesAid Software

"Madiya" wrote in message
ups.com...
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