Formula for if name begins with and date is less than or equal to.

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, on previous post teethless
mama helped me w this, but im still getting a value of 0. Someone please
help!!

=SUMPRODUCT(--(E1:E100=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM"))


Thanks in advance.

Hi,

Your formula works fine for me, what problem are you having?
Are you sure the dates are really dates and not text?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"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, on previous post teethless
mama helped me w this, but im still getting a value of 0. Someone please
help!!

=SUMPRODUCT(--(E1:E100=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM"))


Thanks in advance.

Hi Mike,

Yes, all of Row is in Date format, the problem is that it gives me a
value of 0 and the correct answer should be 7 based on the data on my
spreadsheet.

I did create a counif fomula that gives me the values or all loans that are
less than or equal to 02/05/2010 and it works fine, its not pretty but this
is what i use:
=COUNTIF(Loans!E:E,"="&DATE(1900,1,1))-COUNTIF(Loans!E:E,"="&DATE(2010,2,5))+COUNTIF(Loa ns!E:E,DATE(2010,2,5))
Also I have countif formula to count all loans that begin with FHLM:
=COUNTIF(Loans!U:U,"FHLM*")

But i cant find the right formula to merge these to together.... any ideas
on why im gettin this error??


"Mike H" wrote:

Hi,

Your formula works fine for me, what problem are you having?
Are you sure the dates are really dates and not text?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"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, on previous post teethless
mama helped me w this, but im still getting a value of 0. Someone please
help!!

=SUMPRODUCT(--(E1:E100=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM"))


Thanks in advance.

Hi,

I'm a bit confused about whether you want to sum the 'loans' or simply check
Col E for a date and Col U for a text string, this does that

=SUMPRODUCT((Loans!E1:E12"")*(Loans!E1:E12=DATE (2010,2,5))*(LEFT(Loans!U1:U12,4)="FHLM"))

Now if you want to sum those loans in (say) column T try this

=SUMPRODUCT((Loans!E1:E12"")*(Loans!E1:E12=DATE (2010,2,5))*(LEFT(Loans!U1:U12,4)="FHLM")*(Loans!T 1:T12))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Senor Martinez" wrote:

Hi Mike,

Yes, all of Row is in Date format, the problem is that it gives me a
value of 0 and the correct answer should be 7 based on the data on my
spreadsheet.

I did create a counif fomula that gives me the values or all loans that are
less than or equal to 02/05/2010 and it works fine, its not pretty but this
is what i use:
=COUNTIF(Loans!E:E,"="&DATE(1900,1,1))-COUNTIF(Loans!E:E,"="&DATE(2010,2,5))+COUNTIF(Loa ns!E:E,DATE(2010,2,5))
Also I have countif formula to count all loans that begin with FHLM:
=COUNTIF(Loans!U:U,"FHLM*")

But i cant find the right formula to merge these to together.... any ideas
on why im gettin this error??


"Mike H" wrote:

Hi,

Your formula works fine for me, what problem are you having?
Are you sure the dates are really dates and not text?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"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, on previous post teethless
mama helped me w this, but im still getting a value of 0. Someone please
help!!

=SUMPRODUCT(--(E1:E100=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM"))


Thanks in advance.

Hi (again)

I just noticed you have change the rules since your first post, you now
checking for a date =5/2/2010 so strictly speaking yo don't have to check
for blank cells in column E
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Senor Martinez" wrote:

Hi Mike,

Yes, all of Row is in Date format, the problem is that it gives me a
value of 0 and the correct answer should be 7 based on the data on my
spreadsheet.

I did create a counif fomula that gives me the values or all loans that are
less than or equal to 02/05/2010 and it works fine, its not pretty but this
is what i use:
=COUNTIF(Loans!E:E,"="&DATE(1900,1,1))-COUNTIF(Loans!E:E,"="&DATE(2010,2,5))+COUNTIF(Loa ns!E:E,DATE(2010,2,5))
Also I have countif formula to count all loans that begin with FHLM:
=COUNTIF(Loans!U:U,"FHLM*")

But i cant find the right formula to merge these to together.... any ideas
on why im gettin this error??


"Mike H" wrote:

Hi,

Your formula works fine for me, what problem are you having?
Are you sure the dates are really dates and not text?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"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, on previous post teethless
mama helped me w this, but im still getting a value of 0. Someone please
help!!

=SUMPRODUCT(--(E1:E100=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM"))


Thanks in advance.

Mike!!!!!!

I owe you a 12 pack!!!!!! Thank you so much for your input the
formula works awesome now!!! Thanks again!!!

"Mike H" wrote:

Hi,

I'm a bit confused about whether you want to sum the 'loans' or simply check
Col E for a date and Col U for a text string, this does that

=SUMPRODUCT((Loans!E1:E12"")*(Loans!E1:E12=DATE (2010,2,5))*(LEFT(Loans!U1:U12,4)="FHLM"))

Now if you want to sum those loans in (say) column T try this

=SUMPRODUCT((Loans!E1:E12"")*(Loans!E1:E12=DATE (2010,2,5))*(LEFT(Loans!U1:U12,4)="FHLM")*(Loans!T 1:T12))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Senor Martinez" wrote:

Hi Mike,

Yes, all of Row is in Date format, the problem is that it gives me a
value of 0 and the correct answer should be 7 based on the data on my
spreadsheet.

I did create a counif fomula that gives me the values or all loans that are
less than or equal to 02/05/2010 and it works fine, its not pretty but this
is what i use:
=COUNTIF(Loans!E:E,"="&DATE(1900,1,1))-COUNTIF(Loans!E:E,"="&DATE(2010,2,5))+COUNTIF(Loa ns!E:E,DATE(2010,2,5))
Also I have countif formula to count all loans that begin with FHLM:
=COUNTIF(Loans!U:U,"FHLM*")

But i cant find the right formula to merge these to together.... any ideas
on why im gettin this error??


"Mike H" wrote:

Hi,

Your formula works fine for me, what problem are you having?
Are you sure the dates are really dates and not text?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"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, on previous post teethless
mama helped me w this, but im still getting a value of 0. Someone please
help!!

=SUMPRODUCT(--(E1:E100=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM"))


Thanks in advance.