Counting question

  • Thread starter Thread starter John
  • Start date Start date
J

John

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.
 
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!
 
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.
 
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
 
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
 
Check the reply posted by Mike Sir. It will get the result which you are
expecting to do.
 
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
 
Hi,
I want to count the instances of A in col b when there is a Y in
col a
Click to expand...

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.
 
That doesn't work if the AT & Y need to be in the same row which is how you
question reads
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
You asked for a formula looking for "Y" in column A and "A" in column B
And the answer you stumbled on Looks for "AT" in column E and "Y" in column
C
How did we manage to get that one so wrong.
 
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.
 
May be this...

=SUMPRODUCT(($C$1:$C$200="Y")*($E$1:$E$200="AT"))

Remember to Click Yes, if this post helps!
 
That's it - THANK A BIG BUNCH
--
John


Mike H said:
Hi,


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.
Click to expand...
 
Back
Top