|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 4th, 2010, 10:21 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formaula for less than date and name begins with...!!! HELP!!!
IM trying to create a formula that gives me a total count for cells that are
less than 02/05/10 and the name begins with FHLM, heres what i came up with... =COUNTIF(E:E,"="&DATE(1900,1,1))-COUNTIF(E:E,"="&DATE(2010,2,5))+COUNTIF(E:E,DATE( 2010,2,5))-COUNTIF(U:U,"FHLM*") I keep gettin a bunch of ###### or 0, depending on how I move the data around.. Any ideas???? 
|
|
#2
February 4th, 2010, 11:16 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formaula for less than date and name begins with...!!! HELP!!!
=SUMPRODUCT(--(E1:E100=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM"))
"Senor Martinez" wrote: IM trying to create a formula that gives me a total count for cells that are less than 02/05/10 and the name begins with FHLM, heres what i came up with... =COUNTIF(E:E,"="&DATE(1900,1,1))-COUNTIF(E:E,"="&DATE(2010,2,5))+COUNTIF(E:E,DATE( 2010,2,5))-COUNTIF(U:U,"FHLM*") I keep gettin a bunch of ###### or 0, depending on how I move the data around.. Any ideas????
|
|
#3
February 4th, 2010, 11:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formaula for less than date and name begins with...!!! HELP!!!
Correction:
should be Date(2010,2,5) "Teethless mama" wrote: =SUMPRODUCT(--(E1:E100=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM")) "Senor Martinez" wrote: IM trying to create a formula that gives me a total count for cells that are less than 02/05/10 and the name begins with FHLM, heres what i came up with... =COUNTIF(E:E,"="&DATE(1900,1,1))-COUNTIF(E:E,"="&DATE(2010,2,5))+COUNTIF(E:E,DATE( 2010,2,5))-COUNTIF(U:U,"FHLM*") I keep gettin a bunch of ###### or 0, depending on how I move the data around.. Any ideas????
|
|
#4
February 5th, 2010, 02:35 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formaula for less than date and name begins with...!!! HELP!!!
Thanks mama, but now im getting a value of 0??!!
any other ideas? this is how i typed it =SUMPRODUCT(--(E1:E89000DATE(2010,2,5)),--(LEFT(U1:U89000)="FHLM*")) "Teethless mama" wrote: Correction: should be Date(2010,2,5) "Teethless mama" wrote: =SUMPRODUCT(--(E1:E100=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM")) "Senor Martinez" wrote: IM trying to create a formula that gives me a total count for cells that are less than 02/05/10 and the name begins with FHLM, heres what i came up with... =COUNTIF(E:E,"="&DATE(1900,1,1))-COUNTIF(E:E,"="&DATE(2010,2,5))+COUNTIF(E:E,DATE( 2010,2,5))-COUNTIF(U:U,"FHLM*") I keep gettin a bunch of ###### or 0, depending on how I move the data around.. Any ideas????
|
|
#5
February 5th, 2010, 09:04 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formaula for less than date and name begins with...!!! HELP!!!
Yes, zero is the answer which your formula is certain to return.
Why did you change --(LEFT(U1:U100,4)="FHLM")) to --(LEFT(U1:U89000)="FHLM*")) ? -- David Biddulph Senor Martinez wrote: Thanks mama, but now im getting a value of 0??!! any other ideas? this is how i typed it =SUMPRODUCT(--(E1:E89000DATE(2010,2,5)),--(LEFT(U1:U89000)="FHLM*")) "Teethless mama" wrote: Correction: should be Date(2010,2,5) "Teethless mama" wrote: =SUMPRODUCT(--(E1:E100=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM")) "Senor Martinez" wrote: IM trying to create a formula that gives me a total count for cells that are less than 02/05/10 and the name begins with FHLM, heres what i came up with... =COUNTIF(E:E,"="&DATE(1900,1,1))-COUNTIF(E:E,"="&DATE(2010,2,5))+COUNTIF(E:E,DATE( 2010,2,5))-COUNTIF(U:U,"FHLM*") I keep gettin a bunch of ###### or 0, depending on how I move the data around.. Any ideas????
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
