Counting using OR?

[aaron.hillyer]

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

 

[JE McGimpsey]

One way:

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

 

[Bernd]

Hi Aaron,

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

Regards,
Bernd

 

[aaron.hillyer]

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

 

[aaron.hillyer]

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

 

[JE McGimpsey]

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

 

[Bernd]

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