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.

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

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.

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

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.

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

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.

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

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.

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

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.