How do I count only if either/ or exist

[wildbillyd]

I am attempting to count how many times there is a number greater than zero
in either column A or column B.

My goal is if A=0 and B=0, do not count. If A=0 and B=1, count. If A=1 and
B=1, count only once.

Thank you for any help

 

[Joe User]

My goal is if A=0 and B=0, do not count.
If A=0 and B=1, count. If A=1 and B=1,
count only once.

One way:

=sumproduct(--((A1:A10>0)+(B1:B10>0)>0))

The "+" functions as "OR" in this context. The OR function cannot be used
in this context; it will not be interpreted as intended. The "--" (double
negation) is used to convert the truth values (TRUE and FALSE) to numeric
values (1 and 0), which SUMPRODUCT looks for. Any arithmetic operation with
truth values will accomplish the same thing.


----- original message -----

 

[Rick Rothstein]

What about something like this...

=COUNTIF(A1:A99,">0")+COUNTIF(B1:B99,">0")

 

[Joe User]

What about something like this...
=COUNTIF(A1:A99,">0")+COUNTIF(B1:B99,">0")

Rick, does that meet that OP's condition that "[if] A=1 and B=1, count only
once"?


----- original message -----

 

[T. Valko]

Here's another one...

=SUMPRODUCT(SIGN((A1:A100>0)+(B1:B100>0)))

This version is slightly more efficient on larger ranges.

 

[Rick Rothstein]

No, it doesn't... I skimmed the OP's question too fast and completely missed
that requirement. Thanks for pointing it out.

--
Rick (MVP - Excel)

What about something like this...
=COUNTIF(A1:A99,">0")+COUNTIF(B1:B99,">0")

Rick, does that meet that OP's condition that "[if] A=1 and B=1, count
only once"?


----- original message -----