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 |
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 |
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 |
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 |
Counting using OR?
|
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 |
All times are GMT +1. The time now is 11:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com