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????

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????

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????

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:E89000<DATE(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????

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:E89000<DATE(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????