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
--
AP

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

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

"Madiya" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[[email protected]]

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

--
Don Guillett
SalesAid Software
[email protected]
"Madiya" <[email protected]> wrote in message
news:[email protected]...
>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
>