I have 2 columns and want to get the count of each row where either
one of columns in that row is = "Y" and if both are = "Y", it still
only counts as one "Y". So, with the following (Row #s included at
left to avoid confusion)

Row Column A Column B
1 Y Y
2 Y
3 Y
4
5
6 Y
7 Y
8 Y

We should count 6 (rows 1, 2, 3, 6, 7, and 8 contain a "Y" in either
Column A or B)

NOTE: Rows with two "Y"s should only count once which is where I'm
having trouble using CountA

I'm really trying to avoid using any Macros or code other than built-
in functions if possible as some users will not have these activated
for security reasons.

Thanks!

-Aaron

One way:

=SUMPRODUCT(--ISNUMBER(FIND("Y",A1:A8&B1:B8)))


In article .com,
wrote:

I have 2 columns and want to get the count of each row where either
one of columns in that row is = "Y" and if both are = "Y", it still
only counts as one "Y". So, with the following (Row #s included at
left to avoid confusion)

Row Column A Column B
1 Y Y
2 Y
3 Y
4
5
6 Y
7 Y
8 Y

We should count 6 (rows 1, 2, 3, 6, 7, and 8 contain a "Y" in either
Column A or B)

NOTE: Rows with two "Y"s should only count once which is where I'm
having trouble using CountA

I'm really trying to avoid using any Macros or code other than built-
in functions if possible as some users will not have these activated
for security reasons.

Thanks!

-Aaron

Hi Aaron,

A general approach to apply OR criteria in sumproducts is IMHO
=SUMPRODUCT(SIGN((A1:A8="Y")+(B1:B8="Y")))

Regards,
Bernd

Both of these examples work and are exactly what I'm looking for, so
thanks!!!!

Could you please elaborate on which would be better and why and
perhaps break it down for a newbie to these functions?

Thanks again!!!

-Aaron

Ooops...I lied...this one does NOT work when there is nothing in
Column A but there is in Column B such as in Row 3 below. The other
one posted with the sign function DOES work in this case so I'll use
that. Again, I'd still like an explanation of how specifically that
works so that I can apply it to future functions please. Thanks!

-Aaron


On Feb 20, 12:10 am, JE McGimpsey wrote:
One way:

=SUMPRODUCT(--ISNUMBER(FIND("Y",A1:A8&B1:B8)))

In article .com,

wrote:
I have 2 columns and want to get the count of each row where either
one of columns in that row is = "Y" and if both are = "Y", it still
only counts as one "Y". So, with the following (Row #s included at
left to avoid confusion)

Row Column A Column B
1 Y Y
2 Y
3 Y
4
5
6 Y
7 Y
8 Y

We should count 6 (rows 1, 2, 3, 6, 7, and 8 contain a "Y" in either
Column A or B)

NOTE: Rows with two "Y"s should only count once which is where I'm
having trouble using CountA

I'm really trying to avoid using any Macros or code other than built-
in functions if possible as some users will not have these activated
for security reasons.

Thanks!

-Aaron

Works fine here. What does "does NOT work" mean for you? Do you get an
error? an incorrect total?

In article . com,
wrote:

Ooops...I lied...this one does NOT work when there is nothing in
Column A but there is in Column B such as in Row 3 below. The other
one posted with the sign function DOES work in this case so I'll use
that. Again, I'd still like an explanation of how specifically that
works so that I can apply it to future functions please. Thanks!

-Aaron


On Feb 20, 12:10 am, JE McGimpsey wrote:
One way:

=SUMPRODUCT(--ISNUMBER(FIND("Y",A1:A8&B1:B8)))

In article .com,

wrote:
I have 2 columns and want to get the count of each row where either
one of columns in that row is = "Y" and if both are = "Y", it still
only counts as one "Y". So, with the following (Row #s included at
left to avoid confusion)

Row Column A Column B
1 Y Y
2 Y
3 Y
4
5
6 Y
7 Y
8 Y

We should count 6 (rows 1, 2, 3, 6, 7, and 8 contain a "Y" in either
Column A or B)

NOTE: Rows with two "Y"s should only count once which is where I'm
having trouble using CountA

I'm really trying to avoid using any Macros or code other than built-
in functions if possible as some users will not have these activated
for security reasons.

Thanks!

-Aaron

Hello,

Hmm, both versions should work quite fine.

Actually, JE's version even differentiates between upper case Y and
lower case y (which are NOT counted).

Explanations you can find here, for example:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Regards,
Bernd