A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Counting question



 
 
Thread Tools Display Modes
  #11  
Old June 4th, 2010, 05:18 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 8,419
Default Counting 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.


"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

  #12  
Old June 4th, 2010, 05:20 PM posted to microsoft.public.excel.worksheet.functions
steve
external usenet poster
 
Posts: 28
Default Counting 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.

"John" wrote in message
...
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



  #13  
Old June 4th, 2010, 05:28 PM posted to microsoft.public.excel.worksheet.functions
Ms-Exl-Learner
external usenet poster
 
Posts: 522
Default Counting question

You have to go as per the formula suggested by Mike Sir.

--------------------
(Ms-Exl-Learner)
--------------------


"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

  #14  
Old June 4th, 2010, 05:37 PM posted to microsoft.public.excel.worksheet.functions
John
external usenet poster
 
Posts: 2,649
Default Counting question

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

  #15  
Old June 4th, 2010, 05:41 PM posted to microsoft.public.excel.worksheet.functions
John
external usenet poster
 
Posts: 2,649
Default Counting question

I get the following message in the cell "The formula contains unrecognized
text"
--
John


"steve" wrote:

=COUNTIFS(A:A,"Y",B:B,"A")

Regards
Steve

"John" wrote in message
...
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



.

  #16  
Old June 4th, 2010, 05:46 PM posted to microsoft.public.excel.worksheet.functions
John
external usenet poster
 
Posts: 2,649
Default Counting question

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

  #17  
Old June 4th, 2010, 05:46 PM posted to microsoft.public.excel.worksheet.functions
Ms-Exl-Learner
external usenet poster
 
Posts: 522
Default Counting question

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.