| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif with dates for vs 2003
Hello all,
I have looked through the archives and have seen several formulas that should work in my spreadsheet, but return either a 0 or the wrong number. The dates are entered using 1/6/08 and formatted to appear as Jan-08. I want to count the number of cells that have Jan as a date, etc. I have tried the following: =COUNTIF(Data!F2:F65536,"=1/1/2008") -COUNTIF(Data!F2:F65536,"<=1/31/2008") The result counts all cells rather than the 50 it should be =SUM(COUNTIF(F2:F65536,{"12/31/08","<=1/1/09"})) the result is 0 =SUMPRODUCT((MONTH(Data!F2:F65536)=1),(YEAR(Data!F 2:F65536)=2008)) the result is 0 I am stumped. Would really appreciate the help! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif with dates for vs 2003
How about:
=COUNTIF(Data!F2:F65536,"=date(2008,1,1)) -COUNTIF(Data!F2:F65536,"<=date(2008,1,31)) Or =COUNTIF(Data!F:F,"=date(2008,1,1)) -COUNTIF(Data!F:F,"<=date(2008,1,31)) (I bet you don't have a date in F1) =SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008)) or =SUMPRODUCT(text(Data!F2:F65536,"yyyymm")="200801" ) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Kay wrote: Hello all, I have looked through the archives and have seen several formulas that should work in my spreadsheet, but return either a 0 or the wrong number. The dates are entered using 1/6/08 and formatted to appear as Jan-08. I want to count the number of cells that have Jan as a date, etc. I have tried the following: =COUNTIF(Data!F2:F65536,"=1/1/2008") -COUNTIF(Data!F2:F65536,"<=1/31/2008") The result counts all cells rather than the 50 it should be =SUM(COUNTIF(F2:F65536,{"12/31/08","<=1/1/09"})) the result is 0 =SUMPRODUCT((MONTH(Data!F2:F65536)=1),(YEAR(Data!F 2:F65536)=2008)) the result is 0 I am stumped. Would really appreciate the help! -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif with dates for vs 2003
Kay wrote:
Hello all, I have looked through the archives and have seen several formulas that should work in my spreadsheet, but return either a 0 or the wrong number. The dates are entered using 1/6/08 and formatted to appear as Jan-08. I want to count the number of cells that have Jan as a date, etc. I have tried the following: =COUNTIF(Data!F2:F65536,"=1/1/2008") -COUNTIF(Data!F2:F65536,"<=1/31/2008") The result counts all cells rather than the 50 it should be Shouldn't the "<=" also be "="? =SUM(COUNTIF(F2:F65536,{"12/31/08","<=1/1/09"})) the result is 0 =SUMPRODUCT((MONTH(Data!F2:F65536)=1),(YEAR(Data!F 2:F65536)=2008)) the result is 0 I am stumped. Would really appreciate the help! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif with dates for vs 2003
Glenn wrote:
Kay wrote: Hello all, I have looked through the archives and have seen several formulas that should work in my spreadsheet, but return either a 0 or the wrong number. The dates are entered using 1/6/08 and formatted to appear as Jan-08. I want to count the number of cells that have Jan as a date, etc. I have tried the following: =COUNTIF(Data!F2:F65536,"=1/1/2008") -COUNTIF(Data!F2:F65536,"<=1/31/2008") The result counts all cells rather than the 50 it should be Shouldn't the "<=" also be "="? Also, as pointed out by Dave Peterson, use DATE(year,month,day). = COUNTIF(Data!F2:F65536,"="&DATE(2008,1,1)) - COUNTIF(Data!F2:F65536,"="&DATE(2008,1,31)) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif with dates for vs 2003
DAve,
The first sumproduct example you gave me worked fine so thanks so much, but I would l ike to understand why the examples I sent you did not work. "Dave Peterson" wrote: How about: =COUNTIF(Data!F2:F65536,"=date(2008,1,1)) -COUNTIF(Data!F2:F65536,"<=date(2008,1,31)) Or =COUNTIF(Data!F:F,"=date(2008,1,1)) -COUNTIF(Data!F:F,"<=date(2008,1,31)) (I bet you don't have a date in F1) =SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008)) or =SUMPRODUCT(text(Data!F2:F65536,"yyyymm")="200801" ) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Kay wrote: Hello all, I have looked through the archives and have seen several formulas that should work in my spreadsheet, but return either a 0 or the wrong number. The dates are entered using 1/6/08 and formatted to appear as Jan-08. I want to count the number of cells that have Jan as a date, etc. I have tried the following: =COUNTIF(Data!F2:F65536,"=1/1/2008") -COUNTIF(Data!F2:F65536,"<=1/31/2008") The result counts all cells rather than the 50 it should be =SUM(COUNTIF(F2:F65536,{"12/31/08","<=1/1/09"})) the result is 0 =SUMPRODUCT((MONTH(Data!F2:F65536)=1),(YEAR(Data!F 2:F65536)=2008)) the result is 0 I am stumped. Would really appreciate the help! -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif with dates for vs 2003
I'd guess that stuff like this:
1/1/08 is seen as 1 divided by 1 divided by 8 Not as a real date. Kay wrote: DAve, The first sumproduct example you gave me worked fine so thanks so much, but I would l ike to understand why the examples I sent you did not work. "Dave Peterson" wrote: How about: =COUNTIF(Data!F2:F65536,"=date(2008,1,1)) -COUNTIF(Data!F2:F65536,"<=date(2008,1,31)) Or =COUNTIF(Data!F:F,"=date(2008,1,1)) -COUNTIF(Data!F:F,"<=date(2008,1,31)) (I bet you don't have a date in F1) =SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008)) or =SUMPRODUCT(text(Data!F2:F65536,"yyyymm")="200801" ) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Kay wrote: Hello all, I have looked through the archives and have seen several formulas that should work in my spreadsheet, but return either a 0 or the wrong number. The dates are entered using 1/6/08 and formatted to appear as Jan-08. I want to count the number of cells that have Jan as a date, etc. I have tried the following: =COUNTIF(Data!F2:F65536,"=1/1/2008") -COUNTIF(Data!F2:F65536,"<=1/31/2008") The result counts all cells rather than the 50 it should be =SUM(COUNTIF(F2:F65536,{"12/31/08","<=1/1/09"})) the result is 0 =SUMPRODUCT((MONTH(Data!F2:F65536)=1),(YEAR(Data!F 2:F65536)=2008)) the result is 0 I am stumped. Would really appreciate the help! -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif with dates for vs 2003
DAve,
Your explanation makes sense. thanks, but let me pick your genius brain one more time. If I find my answer using your solution of how many people were hired as contractors, =SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008)) how would i then find out how many of those were converted to permanent employees in the same date range. They might be hired as a contractor and stay that way for several months or they could be permanently hired within the same month. I tried =(SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008))-SUMPRODUCT(--(MONTH(Data!G2:G65536)=1),--(YEAR(Data!G2:G65536)=2008))) but get the wrong answer. "Dave Peterson" wrote: I'd guess that stuff like this: 1/1/08 is seen as 1 divided by 1 divided by 8 Not as a real date. Kay wrote: DAve, The first sumproduct example you gave me worked fine so thanks so much, but I would l ike to understand why the examples I sent you did not work. "Dave Peterson" wrote: How about: =COUNTIF(Data!F2:F65536,"=date(2008,1,1)) -COUNTIF(Data!F2:F65536,"<=date(2008,1,31)) Or =COUNTIF(Data!F:F,"=date(2008,1,1)) -COUNTIF(Data!F:F,"<=date(2008,1,31)) (I bet you don't have a date in F1) =SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008)) or =SUMPRODUCT(text(Data!F2:F65536,"yyyymm")="200801" ) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Kay wrote: Hello all, I have looked through the archives and have seen several formulas that should work in my spreadsheet, but return either a 0 or the wrong number. The dates are entered using 1/6/08 and formatted to appear as Jan-08. I want to count the number of cells that have Jan as a date, etc. I have tried the following: =COUNTIF(Data!F2:F65536,"=1/1/2008") -COUNTIF(Data!F2:F65536,"<=1/31/2008") The result counts all cells rather than the 50 it should be =SUM(COUNTIF(F2:F65536,{"12/31/08","<=1/1/09"})) the result is 0 =SUMPRODUCT((MONTH(Data!F2:F65536)=1),(YEAR(Data!F 2:F65536)=2008)) the result is 0 I am stumped. Would really appreciate the help! -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif with dates for vs 2003
First, I would check to see if this variation of your formula:
=SUMPRODUCT(--(text(Data!F2:F65536,"yyyymm")="200801")) counted the number of contractors hired in Jan of 2008. And this =SUMPRODUCT(--(text(data!G2:G65536,"yyyymm")="200801")) counted the number of permanent employees that started in Jan of 2008. I think this formula is easier to read, but will be equivalent to each portion in your original formula. And as an aside, if you can pick a smaller number of rows to check, your calculation times will be better. But I'm not sure doing the subtraction will get you what you want. If I was hired as a contractor in August of 1967 and made permanent in January of 2008, then I'll be counted in that second formula. If you want to limit your count to just the people hired as contractors in Jan 2008 and converted to permanent employees in Jan of 2008, I think just adding more conditions to the =sumproduct() should work: =SUMPRODUCT(--(text(Data!F2:F65536,"yyyymm")="200801"), --(text(data!G2:G65536,"yyyymm")="200801")) Both of these conditions have to be true for it to be counted. Kay wrote: DAve, Your explanation makes sense. thanks, but let me pick your genius brain one more time. If I find my answer using your solution of how many people were hired as contractors, =SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008)) how would i then find out how many of those were converted to permanent employees in the same date range. They might be hired as a contractor and stay that way for several months or they could be permanently hired within the same month. I tried =(SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008))-SUMPRODUCT(--(MONTH(Data!G2:G65536)=1),--(YEAR(Data!G2:G65536)=2008))) but get the wrong answer. "Dave Peterson" wrote: I'd guess that stuff like this: 1/1/08 is seen as 1 divided by 1 divided by 8 Not as a real date. Kay wrote: DAve, The first sumproduct example you gave me worked fine so thanks so much, but I would l ike to understand why the examples I sent you did not work. "Dave Peterson" wrote: How about: =COUNTIF(Data!F2:F65536,"=date(2008,1,1)) -COUNTIF(Data!F2:F65536,"<=date(2008,1,31)) Or =COUNTIF(Data!F:F,"=date(2008,1,1)) -COUNTIF(Data!F:F,"<=date(2008,1,31)) (I bet you don't have a date in F1) =SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008)) or =SUMPRODUCT(text(Data!F2:F65536,"yyyymm")="200801" ) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Kay wrote: Hello all, I have looked through the archives and have seen several formulas that should work in my spreadsheet, but return either a 0 or the wrong number. The dates are entered using 1/6/08 and formatted to appear as Jan-08. I want to count the number of cells that have Jan as a date, etc. I have tried the following: =COUNTIF(Data!F2:F65536,"=1/1/2008") -COUNTIF(Data!F2:F65536,"<=1/31/2008") The result counts all cells rather than the 50 it should be =SUM(COUNTIF(F2:F65536,{"12/31/08","<=1/1/09"})) the result is 0 =SUMPRODUCT((MONTH(Data!F2:F65536)=1),(YEAR(Data!F 2:F65536)=2008)) the result is 0 I am stumped. Would really appreciate the help! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif with dates for vs 2003
Dave,
Once again thanks. That did simplify the problem because when I tried adding other conditions to the first version, it was just to complex. I really appreciate it. "Dave Peterson" wrote: First, I would check to see if this variation of your formula: =SUMPRODUCT(--(text(Data!F2:F65536,"yyyymm")="200801")) counted the number of contractors hired in Jan of 2008. And this =SUMPRODUCT(--(text(data!G2:G65536,"yyyymm")="200801")) counted the number of permanent employees that started in Jan of 2008. I think this formula is easier to read, but will be equivalent to each portion in your original formula. And as an aside, if you can pick a smaller number of rows to check, your calculation times will be better. But I'm not sure doing the subtraction will get you what you want. If I was hired as a contractor in August of 1967 and made permanent in January of 2008, then I'll be counted in that second formula. If you want to limit your count to just the people hired as contractors in Jan 2008 and converted to permanent employees in Jan of 2008, I think just adding more conditions to the =sumproduct() should work: =SUMPRODUCT(--(text(Data!F2:F65536,"yyyymm")="200801"), --(text(data!G2:G65536,"yyyymm")="200801")) Both of these conditions have to be true for it to be counted. Kay wrote: DAve, Your explanation makes sense. thanks, but let me pick your genius brain one more time. If I find my answer using your solution of how many people were hired as contractors, =SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008)) how would i then find out how many of those were converted to permanent employees in the same date range. They might be hired as a contractor and stay that way for several months or they could be permanently hired within the same month. I tried =(SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008))-SUMPRODUCT(--(MONTH(Data!G2:G65536)=1),--(YEAR(Data!G2:G65536)=2008))) but get the wrong answer. "Dave Peterson" wrote: I'd guess that stuff like this: 1/1/08 is seen as 1 divided by 1 divided by 8 Not as a real date. Kay wrote: DAve, The first sumproduct example you gave me worked fine so thanks so much, but I would l ike to understand why the examples I sent you did not work. "Dave Peterson" wrote: How about: =COUNTIF(Data!F2:F65536,"=date(2008,1,1)) -COUNTIF(Data!F2:F65536,"<=date(2008,1,31)) Or =COUNTIF(Data!F:F,"=date(2008,1,1)) -COUNTIF(Data!F:F,"<=date(2008,1,31)) (I bet you don't have a date in F1) =SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008)) or =SUMPRODUCT(text(Data!F2:F65536,"yyyymm")="200801" ) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Kay wrote: Hello all, I have looked through the archives and have seen several formulas that should work in my spreadsheet, but return either a 0 or the wrong number. The dates are entered using 1/6/08 and formatted to appear as Jan-08. I want to count the number of cells that have Jan as a date, etc. I have tried the following: =COUNTIF(Data!F2:F65536,"=1/1/2008") -COUNTIF(Data!F2:F65536,"<=1/31/2008") The result counts all cells rather than the 50 it should be =SUM(COUNTIF(F2:F65536,{"12/31/08","<=1/1/09"})) the result is 0 =SUMPRODUCT((MONTH(Data!F2:F65536)=1),(YEAR(Data!F 2:F65536)=2008)) the result is 0 I am stumped. Would really appreciate the help! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif and dates | Excel Worksheet Functions | |||
| Countif between two dates | Excel Discussion (Misc queries) | |||
| CountIF with dates | Excel Discussion (Misc queries) | |||
| using countif to add dates | Excel Discussion (Misc queries) | |||
| Countif using dates | Excel Worksheet Functions | |||
Linear Mode
