Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count text cells based on two criteria | Excel Worksheet Functions | |||
Count Non-Blanks in one column based on criteria in another | Excel Worksheet Functions | |||
How do I count nonblank cells that meet criteria in another cell? | Excel Worksheet Functions | |||
count 2 nonblank cells on multiple worksheets | Excel Discussion (Misc queries) | |||
Count nonblank cells with multiple criteria | Excel Worksheet Functions |