Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif with multiple criteria
Hi. I am trying to compose a function in an Excel 2003 spreadsheet that will
count the number of cells that meet two criteria--to count if there is a date in one column AND if there is specific text in another column. Column I contains dates (appearing as, for example, 7-Feb-08, but stored as 2/7/2008) Column E contains text I have been trying to find a function that would do something along the lines of: Count if column I contains a date (and is not blank) AND if column E contains "GC" Any suggestions would be greatly appreciated! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif with multiple criteria
You can only use COUNTIF for one criteria. For more than one you can use
this: =SUMPRODUCT((I1:I1000)*(E1:E100="GC")) Note that to Excel a date is just a number, and that a blank cell is considered as being equivalent to zero in many formulae, so the first term is checking for a number greater than zero in cells I1:I100. If you have more rows than this then you have to change the range accordingly, but you cannot use full-column references in versions before XL2007. If your dates are fairly recent, then you could make the comparison 38000, as this is equivalent to 14th January 2004. Don't be put off by the name of the function - this will COUNT the cells matching the criteria. Hope this helps. Pete "girasole" wrote in message ... Hi. I am trying to compose a function in an Excel 2003 spreadsheet that will count the number of cells that meet two criteria--to count if there is a date in one column AND if there is specific text in another column. Column I contains dates (appearing as, for example, 7-Feb-08, but stored as 2/7/2008) Column E contains text I have been trying to find a function that would do something along the lines of: Count if column I contains a date (and is not blank) AND if column E contains "GC" Any suggestions would be greatly appreciated! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif with multiple criteria
Check out Chip Pearson's site:
http://www.cpearson.com/excel/array.htm -- Hope this helps. If this post was helpfull, please remember to click on the ''''YES'''' button at the bottom of the screen. Thanks, Gary Brown "girasole" wrote: Hi. I am trying to compose a function in an Excel 2003 spreadsheet that will count the number of cells that meet two criteria--to count if there is a date in one column AND if there is specific text in another column. Column I contains dates (appearing as, for example, 7-Feb-08, but stored as 2/7/2008) Column E contains text I have been trying to find a function that would do something along the lines of: Count if column I contains a date (and is not blank) AND if column E contains "GC" Any suggestions would be greatly appreciated! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif with multiple criteria
This is great! Thanks!
"Gary Brown" wrote: Check out Chip Pearson's site: http://www.cpearson.com/excel/array.htm -- Hope this helps. If this post was helpfull, please remember to click on the ''''YES'''' button at the bottom of the screen. Thanks, Gary Brown "girasole" wrote: Hi. I am trying to compose a function in an Excel 2003 spreadsheet that will count the number of cells that meet two criteria--to count if there is a date in one column AND if there is specific text in another column. Column I contains dates (appearing as, for example, 7-Feb-08, but stored as 2/7/2008) Column E contains text I have been trying to find a function that would do something along the lines of: Count if column I contains a date (and is not blank) AND if column E contains "GC" Any suggestions would be greatly appreciated! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif with multiple criteria
Thanks! I had tried using variations of the SUMPRODUCT function, but nothing
worked until your suggestion. Thanks again! "Pete_UK" wrote: You can only use COUNTIF for one criteria. For more than one you can use this: =SUMPRODUCT((I1:I1000)*(E1:E100="GC")) Note that to Excel a date is just a number, and that a blank cell is considered as being equivalent to zero in many formulae, so the first term is checking for a number greater than zero in cells I1:I100. If you have more rows than this then you have to change the range accordingly, but you cannot use full-column references in versions before XL2007. If your dates are fairly recent, then you could make the comparison 38000, as this is equivalent to 14th January 2004. Don't be put off by the name of the function - this will COUNT the cells matching the criteria. Hope this helps. Pete "girasole" wrote in message ... Hi. I am trying to compose a function in an Excel 2003 spreadsheet that will count the number of cells that meet two criteria--to count if there is a date in one column AND if there is specific text in another column. Column I contains dates (appearing as, for example, 7-Feb-08, but stored as 2/7/2008) Column E contains text I have been trying to find a function that would do something along the lines of: Count if column I contains a date (and is not blank) AND if column E contains "GC" Any suggestions would be greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting COUNTIF for multiple criteria in multiple columns | Excel Worksheet Functions | |||
Countif, Multiple criteria | Excel Discussion (Misc queries) | |||
Countif Multiple Criteria | Excel Discussion (Misc queries) | |||
Countif for multiple criteria | Excel Worksheet Functions | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions |