Counting question
in [Functions]
|
Prev: Countif Question
Next: Multiple IF's with an OR
From: John on 4 Jun 2010 12:03 Thanks but this is counting all of the instances of Y when I want to count the insatances where Y is present in col a AND A is present in colb - both are independant of on antother -- 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
From: John on 4 Jun 2010 12:06 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:12 Check the reply posted by Mike Sir. It will get the result which you are expecting to do. -------------------- (Ms-Exl-Learner) -------------------- "John" wrote: > Thanks but this is counting all of the instances of Y when I want to count > the insatances where Y is present in col a AND A is present in colb - both > are independant of on antother > -- > 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
From: John on 4 Jun 2010 12:14 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
From: Mike H on 4 Jun 2010 12:15
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 |