Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting using OR?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting using OR?
One way:
=SUMPRODUCT(--ISNUMBER(FIND("Y",A1:A8&B1:B8))) In article .com, wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting using OR?
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 On Feb 20, 12:10 am, JE McGimpsey wrote: One way: =SUMPRODUCT(--ISNUMBER(FIND("Y",A1:A8&B1:B8))) In article .com, wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting using OR?
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting using OR?
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting using OR?
Hi Aaron,
A general approach to apply OR criteria in sumproducts is IMHO =SUMPRODUCT(SIGN((A1:A8="Y")+(B1:B8="Y"))) Regards, Bernd |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting using OR?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting | Excel Discussion (Misc queries) | |||
Counting help | Excel Discussion (Misc queries) | |||
Counting rows, then counting values. | Excel Discussion (Misc queries) | |||
Counting Help | Excel Worksheet Functions | |||
Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) |