IF Statements and multiple columns
I want to add the number of times "Y" appears in a column, based upon the
date in another column. Here is my layout: Col A Col B Class Date Graduated? 8/4/2005 Y 8/4/2005 Y 8/4/2005 N 11/2/2005 Y 11/2/2005 Y I want to know for how many who attended each class date, the number who graduated . This is what I tried =IF(A1:A5,"8/4/2005",IF(B1:B5,"Y",CountA(B1:B5))) It apepars at first to work (I get no error message), but I don't get a value in the cell - I get #Value! error message instead. I also tried: =IF(A1:A5,"8/4/2005",COUNTIF(B1:b5,"Y")) Same #Value! error in cell Any help would be most appreciated! Thanks, Tina Hudson |
IF Statements and multiple columns
Assuming your data starts in Row 2 and the Cell C2 contains the date you
want to check (this allows you to make multiple queries by simply changing its value), you could use this formula... =SUMPRODUCT((A2:A1000=C2)*(B2:B1000="Y")) Adjust the maxium range value (row 1000 here) to suit your data. Rick "Tina Hudson" wrote in message ... I want to add the number of times "Y" appears in a column, based upon the date in another column. Here is my layout: Col A Col B Class Date Graduated? 8/4/2005 Y 8/4/2005 Y 8/4/2005 N 11/2/2005 Y 11/2/2005 Y I want to know for how many who attended each class date, the number who graduated . This is what I tried =IF(A1:A5,"8/4/2005",IF(B1:B5,"Y",CountA(B1:B5))) It apepars at first to work (I get no error message), but I don't get a value in the cell - I get #Value! error message instead. I also tried: =IF(A1:A5,"8/4/2005",COUNTIF(B1:b5,"Y")) Same #Value! error in cell Any help would be most appreciated! Thanks, Tina Hudson |
IF Statements and multiple columns
Rick,
Thanks! I also realized that since they all graduate (well, those who do), graduate on the same date, I was able to do the following and get the same result as your suggestion: =COUNTIF(I11:I133,"9/22/2005") However, what I now need is to know how many of those who graduated, actually were licensed. Unlike with the graduation date, licensing can be on different dates. So, should I just insert another column and type in "Y" for those who get licensed and use your formula, or, is there a way to do this with a date in the column. I'd like to keep the number of columns down as these aren't the only columns in the spreadsheet. Here is the full layout (first line of text is A2 and Dates are set up as date fields) Col A Col B Col C Class Date Graduated Date Licensed Date 8/4/2005 9/22/2005 11/19/2005 8/4/2005 9/22/2005 8/4/2005 11/2/2005 12/4/2005 1/28/2006 11/2/2005 -- Thanks, Tina Hudson "Rick Rothstein (MVP - VB)" wrote: Assuming your data starts in Row 2 and the Cell C2 contains the date you want to check (this allows you to make multiple queries by simply changing its value), you could use this formula... =SUMPRODUCT((A2:A1000=C2)*(B2:B1000="Y")) Adjust the maxium range value (row 1000 here) to suit your data. Rick "Tina Hudson" wrote in message ... I want to add the number of times "Y" appears in a column, based upon the date in another column. Here is my layout: Col A Col B Class Date Graduated? 8/4/2005 Y 8/4/2005 Y 8/4/2005 N 11/2/2005 Y 11/2/2005 Y I want to know for how many who attended each class date, the number who graduated . This is what I tried =IF(A1:A5,"8/4/2005",IF(B1:B5,"Y",CountA(B1:B5))) It apepars at first to work (I get no error message), but I don't get a value in the cell - I get #Value! error message instead. I also tried: =IF(A1:A5,"8/4/2005",COUNTIF(B1:b5,"Y")) Same #Value! error in cell Any help would be most appreciated! Thanks, Tina Hudson |
IF Statements and multiple columns
=SUMPRODUCT((B2:B1000=D2)*(C2:C1000<""))
D2=Graduation date "Tina Hudson" wrote: Rick, Thanks! I also realized that since they all graduate (well, those who do), graduate on the same date, I was able to do the following and get the same result as your suggestion: =COUNTIF(I11:I133,"9/22/2005") However, what I now need is to know how many of those who graduated, actually were licensed. Unlike with the graduation date, licensing can be on different dates. So, should I just insert another column and type in "Y" for those who get licensed and use your formula, or, is there a way to do this with a date in the column. I'd like to keep the number of columns down as these aren't the only columns in the spreadsheet. Here is the full layout (first line of text is A2 and Dates are set up as date fields) Col A Col B Col C Class Date Graduated Date Licensed Date 8/4/2005 9/22/2005 11/19/2005 8/4/2005 9/22/2005 8/4/2005 11/2/2005 12/4/2005 1/28/2006 11/2/2005 -- Thanks, Tina Hudson "Rick Rothstein (MVP - VB)" wrote: Assuming your data starts in Row 2 and the Cell C2 contains the date you want to check (this allows you to make multiple queries by simply changing its value), you could use this formula... =SUMPRODUCT((A2:A1000=C2)*(B2:B1000="Y")) Adjust the maxium range value (row 1000 here) to suit your data. Rick "Tina Hudson" wrote in message ... I want to add the number of times "Y" appears in a column, based upon the date in another column. Here is my layout: Col A Col B Class Date Graduated? 8/4/2005 Y 8/4/2005 Y 8/4/2005 N 11/2/2005 Y 11/2/2005 Y I want to know for how many who attended each class date, the number who graduated . This is what I tried =IF(A1:A5,"8/4/2005",IF(B1:B5,"Y",CountA(B1:B5))) It apepars at first to work (I get no error message), but I don't get a value in the cell - I get #Value! error message instead. I also tried: =IF(A1:A5,"8/4/2005",COUNTIF(B1:b5,"Y")) Same #Value! error in cell Any help would be most appreciated! Thanks, Tina Hudson |
IF Statements and multiple columns
Worked like a charm!
Have a good one -- Thanks, Tina Hudson "Toppers" wrote: =SUMPRODUCT((B2:B1000=D2)*(C2:C1000<"")) D2=Graduation date "Tina Hudson" wrote: Rick, Thanks! I also realized that since they all graduate (well, those who do), graduate on the same date, I was able to do the following and get the same result as your suggestion: =COUNTIF(I11:I133,"9/22/2005") However, what I now need is to know how many of those who graduated, actually were licensed. Unlike with the graduation date, licensing can be on different dates. So, should I just insert another column and type in "Y" for those who get licensed and use your formula, or, is there a way to do this with a date in the column. I'd like to keep the number of columns down as these aren't the only columns in the spreadsheet. Here is the full layout (first line of text is A2 and Dates are set up as date fields) Col A Col B Col C Class Date Graduated Date Licensed Date 8/4/2005 9/22/2005 11/19/2005 8/4/2005 9/22/2005 8/4/2005 11/2/2005 12/4/2005 1/28/2006 11/2/2005 -- Thanks, Tina Hudson "Rick Rothstein (MVP - VB)" wrote: Assuming your data starts in Row 2 and the Cell C2 contains the date you want to check (this allows you to make multiple queries by simply changing its value), you could use this formula... =SUMPRODUCT((A2:A1000=C2)*(B2:B1000="Y")) Adjust the maxium range value (row 1000 here) to suit your data. Rick "Tina Hudson" wrote in message ... I want to add the number of times "Y" appears in a column, based upon the date in another column. Here is my layout: Col A Col B Class Date Graduated? 8/4/2005 Y 8/4/2005 Y 8/4/2005 N 11/2/2005 Y 11/2/2005 Y I want to know for how many who attended each class date, the number who graduated . This is what I tried =IF(A1:A5,"8/4/2005",IF(B1:B5,"Y",CountA(B1:B5))) It apepars at first to work (I get no error message), but I don't get a value in the cell - I get #Value! error message instead. I also tried: =IF(A1:A5,"8/4/2005",COUNTIF(B1:b5,"Y")) Same #Value! error in cell Any help would be most appreciated! Thanks, Tina Hudson |
All times are GMT +1. The time now is 04:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com