Counting question
in [Functions]
|
Prev: Countif Question
Next: Multiple IF's with an OR
From: John on 4 Jun 2010 12:46 That's it - THANK A BIG BUNCH -- John "Mike H" wrote: > Hi, > > >I want to count the instances of A in col b when there is a Y in > > col a > > That is exactly what my formula does and if it isn't working then my guess > is you entered it incorrectly > > This for example will return #NA because the ranges are different sizes > =SUMPRODUCT((A1:A20="Y")*(B1:B21="A")) > > > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "John" wrote: > > > Thanks but I get #N/A the variables in col a and b are independant of one > > another and I want to count the instances of A in col b when there is a Y in > > col a both can occur in a range of rows like row 1:200 > > -- > > John > > > > > > "Mike H" wrote: > > > > > John. > > > > > > try this > > > > > > =SUMPRODUCT((A1:A20="Y")*(B1:B20="A")) > > > -- > > > Mike > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > introduces the fewest assumptions while still sufficiently answering the > > > question. > > > > > > > > > "John" wrote: > > > > > > > In column A I have set of characters that are entered into the rows below > > > > (i.e. Y, N, MRO) in column B I have another set of characters that are > > > > entered into the rows below (i.e. A, E, on so on). I want to count the > > > > instances when the rows in Column A contain a Y and the rows in column B > > > > contain an A. Is there an simple formula for doing this? Any help will be > > > > appreciated. > > > > -- > > > > John
From: Ms-Exl-Learner on 4 Jun 2010 12:46
May be this... =SUMPRODUCT(($C$1:$C$200="Y")*($E$1:$E$200="AT")) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "John" wrote: > Apparently there is a flaw in what I thought was the solution. i looks like > the modified formula is simply counting all instances of "AT" regardless of > the presence of any character in col C. I'm still looking for a solution, > please. > -- > John > > > "John" wrote: > > > I think I stumbled onto the solution using your formula but modifying it to > > =COUNTIF(E:E,"AT")+IF(C:C,"Y") I prooved the accuracy of the count by > > manually checking. > > > > Thanks > > > > -- > > John > > > > > > "Ms-Exl-Learner" wrote: > > > > > Put this formula other than A&B Column cell. > > > > > > =COUNTIF(A:A,"Y")+COUNTIF(B:B,"A") > > > > > > Remember to Click Yes, if this post helps! > > > > > > -------------------- > > > (Ms-Exl-Learner) > > > -------------------- > > > > > > > > > "John" wrote: > > > > > > > In column A I have set of characters that are entered into the rows below > > > > (i.e. Y, N, MRO) in column B I have another set of characters that are > > > > entered into the rows below (i.e. A, E, on so on). I want to count the > > > > instances when the rows in Column A contain a Y and the rows in column B > > > > contain an A. Is there an simple formula for doing this? Any help will be > > > > appreciated. > > > > -- > > > > John |