How do I count only if either/ or exist

  • Thread starter Thread starter wildbillyd
  • Start date Start date
W

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
 
wildbillyd said:
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.
Click to expand...

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 said:
What about something like this...
=COUNTIF(A1:A99,">0")+COUNTIF(B1:B99,">0")
Click to expand...

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


----- original message -----
 
Here's another one...

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

This version is slightly more efficient on larger ranges.
 
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)


Joe User said:
Rick Rothstein said:
What about something like this...
=COUNTIF(A1:A99,">0")+COUNTIF(B1:B99,">0")
Click to expand...

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


----- original message -----
Click to expand...
 
Back
Top