Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count only if either/ or exist
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count only if either/ or exist
"wildbillyd" wrote:
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:A100)+(B1:B100)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 ----- "wildbillyd" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count only if either/ or exist
What about something like this...
=COUNTIF(A1:A99,"0")+COUNTIF(B1:B99,"0") -- Rick (MVP - Excel) "wildbillyd" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count only if either/ or exist
"Rick Rothstein" wrote in message
... 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 ----- "Rick Rothstein" wrote in message ... What about something like this... =COUNTIF(A1:A99,"0")+COUNTIF(B1:B99,"0") -- Rick (MVP - Excel) "wildbillyd" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count only if either/ or exist
Here's another one...
=SUMPRODUCT(SIGN((A1:A1000)+(B1:B1000))) This version is slightly more efficient on larger ranges. -- Biff Microsoft Excel MVP "wildbillyd" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count only if either/ or exist
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" <joeu2004 wrote in message ... "Rick Rothstein" wrote in message ... 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 ----- "Rick Rothstein" wrote in message ... What about something like this... =COUNTIF(A1:A99,"0")+COUNTIF(B1:B99,"0") -- Rick (MVP - Excel) "wildbillyd" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I return a unique count where conditions exist? | Excel Worksheet Functions | |||
Count if multiple variable exist | Excel Discussion (Misc queries) | |||
Exist or Not. | Excel Discussion (Misc queries) | |||
Count if 3 different conditions exist | Excel Discussion (Misc queries) | |||
count how often a name exist | Excel Worksheet Functions |