Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22
Default Count no. of nonblank cells in one column based on criteria of ano

I need to count the number of nonblank cells in one column based on criteria
from cells in another column. Example: Column A contains years 2006, 2007,
2008. Column B contains rank such as Lt., Col., etc. I need to know the
total number of 2006 individuals who have the rank of Lt., the number of 2006
individuals who have the rank of Col., etc.

I can get the count of non-blank cells but cannot figure out how to have it
look at the other column for the type of count I need.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,726
Default Count no. of nonblank cells in one column based on criteria of ano

=sumproduct(--($A$2:$A$200=2006),--($B$2:$B$200="Lt")

etc.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Beach Lover" wrote in message
...
I need to count the number of nonblank cells in one column based on
criteria
from cells in another column. Example: Column A contains years 2006,
2007,
2008. Column B contains rank such as Lt., Col., etc. I need to know the
total number of 2006 individuals who have the rank of Lt., the number of
2006
individuals who have the rank of Col., etc.

I can get the count of non-blank cells but cannot figure out how to have
it
look at the other column for the type of count I need.



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default Count no. of nonblank cells in one column based on criteria of ano

=SUMPRODUCT((A1:A12=2006)*(B1:B12="Lt"))

Will return the number of Lt for 2006. The formula can be copied and pasted
into other cells to return for example 2007 - Col

"Beach Lover" wrote:

I need to count the number of nonblank cells in one column based on criteria
from cells in another column. Example: Column A contains years 2006, 2007,
2008. Column B contains rank such as Lt., Col., etc. I need to know the
total number of 2006 individuals who have the rank of Lt., the number of 2006
individuals who have the rank of Col., etc.

I can get the count of non-blank cells but cannot figure out how to have it
look at the other column for the type of count I need.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22
Default Count no. of nonblank cells in one column based on criteria of

This worked great! Thank you so much!

"Bob Phillips" wrote:

=sumproduct(--($A$2:$A$200=2006),--($B$2:$B$200="Lt")

etc.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Beach Lover" wrote in message
...
I need to count the number of nonblank cells in one column based on
criteria
from cells in another column. Example: Column A contains years 2006,
2007,
2008. Column B contains rank such as Lt., Col., etc. I need to know the
total number of 2006 individuals who have the rank of Lt., the number of
2006
individuals who have the rank of Col., etc.

I can get the count of non-blank cells but cannot figure out how to have
it
look at the other column for the type of count I need.




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22
Default Count no. of nonblank cells in one column based on criteria of

Forgot to ask.....how would you search HELP and get this information?

"Bob Phillips" wrote:

=sumproduct(--($A$2:$A$200=2006),--($B$2:$B$200="Lt")

etc.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Beach Lover" wrote in message
...
I need to count the number of nonblank cells in one column based on
criteria
from cells in another column. Example: Column A contains years 2006,
2007,
2008. Column B contains rank such as Lt., Col., etc. I need to know the
total number of 2006 individuals who have the rank of Lt., the number of
2006
individuals who have the rank of Col., etc.

I can get the count of non-blank cells but cannot figure out how to have
it
look at the other column for the type of count I need.






  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default Count no. of nonblank cells in one column based on criteria of ano

An excellent turorial on sumproduct

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

"Beach Lover" wrote:

I need to count the number of nonblank cells in one column based on criteria
from cells in another column. Example: Column A contains years 2006, 2007,
2008. Column B contains rank such as Lt., Col., etc. I need to know the
total number of 2006 individuals who have the rank of Lt., the number of 2006
individuals who have the rank of Col., etc.

I can get the count of non-blank cells but cannot figure out how to have it
look at the other column for the type of count I need.

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6,953
Default Count no. of nonblank cells in one column based on criteria of

Help doesn't have that information.

--
Regards,
Tom Ogilvy


"Beach Lover" wrote:

Forgot to ask.....how would you search HELP and get this information?

"Bob Phillips" wrote:

=sumproduct(--($A$2:$A$200=2006),--($B$2:$B$200="Lt")

etc.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Beach Lover" wrote in message
...
I need to count the number of nonblank cells in one column based on
criteria
from cells in another column. Example: Column A contains years 2006,
2007,
2008. Column B contains rank such as Lt., Col., etc. I need to know the
total number of 2006 individuals who have the rank of Lt., the number of
2006
individuals who have the rank of Col., etc.

I can get the count of non-blank cells but cannot figure out how to have
it
look at the other column for the type of count I need.




  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22
Default Count no. of nonblank cells in one column based on criteria of

So how would I find this information? Other than the way I found it by using
the discussion group? I am not a programmer but like to be able to find
answers for myself, if possible.

"Tom Ogilvy" wrote:

Help doesn't have that information.

--
Regards,
Tom Ogilvy


"Beach Lover" wrote:

Forgot to ask.....how would you search HELP and get this information?

"Bob Phillips" wrote:

=sumproduct(--($A$2:$A$200=2006),--($B$2:$B$200="Lt")

etc.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Beach Lover" wrote in message
...
I need to count the number of nonblank cells in one column based on
criteria
from cells in another column. Example: Column A contains years 2006,
2007,
2008. Column B contains rank such as Lt., Col., etc. I need to know the
total number of 2006 individuals who have the rank of Lt., the number of
2006
individuals who have the rank of Col., etc.

I can get the count of non-blank cells but cannot figure out how to have
it
look at the other column for the type of count I need.



  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22
Default Count no. of nonblank cells in one column based on criteria of

Thanks for the link. I just wish I understood the language a little better!

"Mike" wrote:

An excellent turorial on sumproduct

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

"Beach Lover" wrote:

I need to count the number of nonblank cells in one column based on criteria
from cells in another column. Example: Column A contains years 2006, 2007,
2008. Column B contains rank such as Lt., Col., etc. I need to know the
total number of 2006 individuals who have the rank of Lt., the number of 2006
individuals who have the rank of Col., etc.

I can get the count of non-blank cells but cannot figure out how to have it
look at the other column for the type of count I need.

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,726
Default Count no. of nonblank cells in one column based on criteria of

See http://xldynamic.com/source/xld.SUMPRODUCT.html

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Beach Lover" wrote in message
...
So how would I find this information? Other than the way I found it by
using
the discussion group? I am not a programmer but like to be able to find
answers for myself, if possible.

"Tom Ogilvy" wrote:

Help doesn't have that information.

--
Regards,
Tom Ogilvy


"Beach Lover" wrote:

Forgot to ask.....how would you search HELP and get this information?

"Bob Phillips" wrote:

=sumproduct(--($A$2:$A$200=2006),--($B$2:$B$200="Lt")

etc.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Beach Lover" wrote in message
...
I need to count the number of nonblank cells in one column based on
criteria
from cells in another column. Example: Column A contains years
2006,
2007,
2008. Column B contains rank such as Lt., Col., etc. I need to
know the
total number of 2006 individuals who have the rank of Lt., the
number of
2006
individuals who have the rank of Col., etc.

I can get the count of non-blank cells but cannot figure out how to
have
it
look at the other column for the type of count I need.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count text cells based on two criteria aet999 Excel Worksheet Functions 9 May 2nd 09 03:20 AM
Count Non-Blanks in one column based on criteria in another ScottPcola Excel Worksheet Functions 3 November 3rd 06 11:11 PM
How do I count nonblank cells that meet criteria in another cell? jimswinder Excel Worksheet Functions 13 July 20th 06 04:42 PM
count 2 nonblank cells on multiple worksheets April Excel Discussion (Misc queries) 1 April 18th 06 09:15 AM
Count nonblank cells with multiple criteria Daniel Excel Worksheet Functions 5 November 17th 05 10:59 PM


All times are GMT +1. The time now is 06:34 AM.

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

About Us

"It's about Microsoft Excel"