Counting using OR?

  • Thread starter Thread starter aaron.hillyer
  • Start date Start date
A

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
 
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
 
Works fine here. What does "does NOT work" mean for you? Do you get an
error? an incorrect total?
 
Back
Top