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

[Senor Martinez]

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 H]

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]

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(Loans!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

 

[Mike H]

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!T1:T12))
--
Mike

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

 

[Mike H]

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]

Mike!!!!!!

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