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

  • Thread starter Thread starter Senor Martinez
  • Start date Start date
S

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.
 
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.
 
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
 
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.
 
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.
 
Mike!!!!!!

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