Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count IF multiple criteria
I hope someone can PLEASE help me :)
How do you get a countif to work within a countif's results? I want it to count how many cells within a range contain certain text and within the results of that I want it to tell me how many fall within a range of dates. This is to be reported on a seperate spreadsheet within the workbook so filtering isn't an option. I thought the below formula would work but it actually adds the count of cells that contain the text "yhard" with the count of cells that fall within the date range. =COUNTIF('January- STD'!$C$7:$C269,"*yhard*")+((COUNTIF('January- STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF($G$9:$G$155,"<01/01/2008"))) Is there a way to connect an AND and have the count ifs done in sequential order by connecting the following 2 formulas? #1 criteria =COUNTIF('January- STD'!$C$7:$C269,"*yhard*") # 2 criteria =COUNTIF('January- STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF('January- STD'!$G$9:$G$155,"<01/01/2008") Thank you thank you thank you in advance if there is a genious out there that can help me solve my problem! :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count IF multiple criteria
COUNTIF is limited to a single condition - if you have more, then you
can use SUMPRODUCT like this: =SUMPRODUCT((ISNUMBER(SEARCH("yhard",'January- STD'!$C$7:$C269)))* ('January- STD'!$G$7:$G$269<=--"01/30/2008")*('January- STD'!$G$7:$G$269--"01/01/2008")) Hope this helps. Pete On Dec 11, 4:53*pm, Sarah Barah <Sarah wrote: I hope someone can PLEASE help me :) How do you get a countif to work within a countif's results? *I want it to count how many cells within a range contain certain text and within the results of that I want it to tell me how many fall within a range of dates. * This is to be reported on a seperate spreadsheet within the workbook so filtering isn't an option. I thought the below formula would work but it actually adds the count of cells that contain the text "yhard" with the count of cells that fall within the date range. =COUNTIF('January- STD'!$C$7:$C269,"*yhard*")+((COUNTIF('January- STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF($G$9:$G$155,"<01/01/2008"))) Is there a way to connect an AND and have the count ifs done in sequential order by connecting the following 2 formulas? #1 criteria * =COUNTIF('January- STD'!$C$7:$C269,"*yhard*") # 2 criteria *=COUNTIF('January- STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF('January- STD'!$G$9:$G$155,"<01/01/2008") Thank you thank you thank you in advance if there is a genious out there that can help me solve my problem! :) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count IF multiple criteria
That formula won't work but you have a problem before I can make a
suggestion that will work: the ranges should (read: have to) be the same size: $C$7:$C269 $G$9:$G$155 -- Biff Microsoft Excel MVP "Sarah Barah" <Sarah wrote in message ... I hope someone can PLEASE help me :) How do you get a countif to work within a countif's results? I want it to count how many cells within a range contain certain text and within the results of that I want it to tell me how many fall within a range of dates. This is to be reported on a seperate spreadsheet within the workbook so filtering isn't an option. I thought the below formula would work but it actually adds the count of cells that contain the text "yhard" with the count of cells that fall within the date range. =COUNTIF('January- STD'!$C$7:$C269,"*yhard*")+((COUNTIF('January- STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF($G$9:$G$155,"<01/01/2008"))) Is there a way to connect an AND and have the count ifs done in sequential order by connecting the following 2 formulas? #1 criteria =COUNTIF('January- STD'!$C$7:$C269,"*yhard*") # 2 criteria =COUNTIF('January- STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF('January- STD'!$G$9:$G$155,"<01/01/2008") Thank you thank you thank you in advance if there is a genious out there that can help me solve my problem! :) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count IF multiple criteria
What you're looking for there is a sumproduct.
=COUNTIF('January- STD'!$C$7:$C269,"*yhard*")+((COUNTIF('January- STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF($G$9:$G$155,"<01/01/2008"))) =SUMPRODUCT(--('January- STD'!$C$7:$C269="*yhard*"),--('January- STD'!$G$7:$G$269<=01/30/2008),--('January- STD'!$G$7:$G$26912/31/2007)) Will count all rows where the string yhard is found within column C and the date in column g is from January 2008. Please note.... All ranges must be equal, thus 7 - 269 in all 3 pieces. the -- makes each value either a 0 or a 1 based on if the cell matches the criterion. In the above, you MUST use a range. Don't try using C:C, must be C7-C269 or some such... HTH! "Sarah Barah" wrote: I hope someone can PLEASE help me :) How do you get a countif to work within a countif's results? I want it to count how many cells within a range contain certain text and within the results of that I want it to tell me how many fall within a range of dates. This is to be reported on a seperate spreadsheet within the workbook so filtering isn't an option. I thought the below formula would work but it actually adds the count of cells that contain the text "yhard" with the count of cells that fall within the date range. =COUNTIF('January- STD'!$C$7:$C269,"*yhard*")+((COUNTIF('January- STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF($G$9:$G$155,"<01/01/2008"))) Is there a way to connect an AND and have the count ifs done in sequential order by connecting the following 2 formulas? #1 criteria =COUNTIF('January- STD'!$C$7:$C269,"*yhard*") # 2 criteria =COUNTIF('January- STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF('January- STD'!$G$9:$G$155,"<01/01/2008") Thank you thank you thank you in advance if there is a genious out there that can help me solve my problem! :) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count IF multiple criteria
Sean,
a few things wrong with your suggested formula - you can't use wildcards like that, and your first "date" will be evaluated as 1 divided by 30 divided by 2008, so it's not going to work. Pete On Dec 11, 5:43*pm, Sean Timmons wrote: What you're looking for there is a sumproduct. =COUNTIF('January- STD'!$C$7:$C269,"*yhard*")+((COUNTIF('January- STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF($G$9:$G$155,"<01/01/2008"))) =SUMPRODUCT(--('January- STD'!$C$7:$C269="*yhard*"),--('January- STD'!$G$7:$G$269<=01/30/2008),--('January- STD'!$G$7:$G$26912/31/2007)) Will count all rows where the string yhard is found within column C and the date in column g is from January 2008. Please note.... All ranges must be equal, thus 7 - 269 in all 3 pieces. the -- makes each value either a 0 or a 1 based on if the cell matches the criterion. In the above, you MUST use a range. Don't try using C:C, must be C7-C269 or some such... HTH! "Sarah Barah" wrote: I hope someone can PLEASE help me :) How do you get a countif to work within a countif's results? *I want it to count how many cells within a range contain certain text and within the results of that I want it to tell me how many fall within a range of dates. * This is to be reported on a seperate spreadsheet within the workbook so filtering isn't an option. I thought the below formula would work but it actually adds the count of cells that contain the text "yhard" with the count of cells that fall within the date range. =COUNTIF('January- STD'!$C$7:$C269,"*yhard*")+((COUNTIF('January- STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF($G$9:$G$155,"<01/01/2008"))) Is there a way to connect an AND and have the count ifs done in sequential order by connecting the following 2 formulas? #1 criteria * =COUNTIF('January- STD'!$C$7:$C269,"*yhard*") # 2 criteria *=COUNTIF('January- STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF('January- STD'!$G$9:$G$155,"<01/01/2008") Thank you thank you thank you in advance if there is a genious out there that can help me solve my problem! :)- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count IF multiple criteria
:)Thank you Pete from the UK! You are the winning genious! Your below
formula works... I dont really understand the ISNUMBER and SEARCH in terms of how but as long as I get the correct answer I am happy. This saves me hours upon hours of work. Thanks again! "Pete_UK" wrote: COUNTIF is limited to a single condition - if you have more, then you can use SUMPRODUCT like this: =SUMPRODUCT((ISNUMBER(SEARCH("yhard",'January- STD'!$C$7:$C269)))* ('January- STD'!$G$7:$G$269<=--"01/30/2008")*('January- STD'!$G$7:$G$269--"01/01/2008")) Hope this helps. Pete On Dec 11, 4:53 pm, Sarah Barah <Sarah wrote: I hope someone can PLEASE help me :) How do you get a countif to work within a countif's results? I want it to count how many cells within a range contain certain text and within the results of that I want it to tell me how many fall within a range of dates. This is to be reported on a seperate spreadsheet within the workbook so filtering isn't an option. I thought the below formula would work but it actually adds the count of cells that contain the text "yhard" with the count of cells that fall within the date range. =COUNTIF('January- STD'!$C$7:$C269,"*yhard*")+((COUNTIF('January- STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF($G$9:$G$155,"<01/01/2008"))) Is there a way to connect an AND and have the count ifs done in sequential order by connecting the following 2 formulas? #1 criteria =COUNTIF('January- STD'!$C$7:$C269,"*yhard*") # 2 criteria =COUNTIF('January- STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF('January- STD'!$G$9:$G$155,"<01/01/2008") Thank you thank you thank you in advance if there is a genious out there that can help me solve my problem! :) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count IF multiple criteria
You're welcome, Sarah - thanks for your kind words, though I didn't know it
was a competition !! <bg The ISNUMBER(SEARCH(... construct is needed because we can't use wildcards in this formula, but basically it means that the cell should be counted if the string "yhard" is contained in the cell. The asterisks are equivalent to AND, so you could express the formula as meaning "Using rows 7 to 269, count all the cells where "yhard" is contained in column C AND the date is after 1st Jan 2008 in column G AND the date is before 31st Jan 2008 in column G". Note that this does not include 1st Jan 2008 - you can change the to = if you want to include that date. Pete "Sarah Barah" wrote in message ... :)Thank you Pete from the UK! You are the winning genious! Your below formula works... I dont really understand the ISNUMBER and SEARCH in terms of how but as long as I get the correct answer I am happy. This saves me hours upon hours of work. Thanks again! "Pete_UK" wrote: COUNTIF is limited to a single condition - if you have more, then you can use SUMPRODUCT like this: =SUMPRODUCT((ISNUMBER(SEARCH("yhard",'January- STD'!$C$7:$C269)))* ('January- STD'!$G$7:$G$269<=--"01/30/2008")*('January- STD'!$G$7:$G$269--"01/01/2008")) Hope this helps. Pete On Dec 11, 4:53 pm, Sarah Barah <Sarah wrote: I hope someone can PLEASE help me :) How do you get a countif to work within a countif's results? I want it to count how many cells within a range contain certain text and within the results of that I want it to tell me how many fall within a range of dates. This is to be reported on a seperate spreadsheet within the workbook so filtering isn't an option. I thought the below formula would work but it actually adds the count of cells that contain the text "yhard" with the count of cells that fall within the date range. =COUNTIF('January- STD'!$C$7:$C269,"*yhard*")+((COUNTIF('January- STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF($G$9:$G$155,"<01/01/2008"))) Is there a way to connect an AND and have the count ifs done in sequential order by connecting the following 2 formulas? #1 criteria =COUNTIF('January- STD'!$C$7:$C269,"*yhard*") # 2 criteria =COUNTIF('January- STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF('January- STD'!$G$9:$G$155,"<01/01/2008") Thank you thank you thank you in advance if there is a genious out there that can help me solve my problem! :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count multiple cells against multiple criteria in an Excel spreads | Excel Worksheet Functions | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
COUNT WITH MULTIPLE CRITERIA | Excel Worksheet Functions | |||
COUNT or SUM with multiple criteria | Excel Worksheet Functions | |||
I need to count while using multiple criteria | Excel Worksheet Functions |