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.

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.

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.

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.

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.

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.

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.

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.

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.

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.