Posted by Dallas on July 24, 2001 2:13 PM

Out of a list of license plates I want to count how many are rentals and that have been paid after 14 days. There are also fields that have no value in the amount of days column so I had to subtract them from the total. License plates are LLL### (L=Letter)and rentals are L#####. This is my formula that I came up with.

=SUM((AND((VALUE(LEFT(RIGHT(D2:D201,5),1))>0),(VALUE(LEFT(RIGHT(D2:D201,5),1))&LT;9)))+((H2:H201>14)-(H2:H201="")))

I think my problem is the and but I'm not sure what to use.

Help!

Posted by Aladin Akyurek on July 24, 2001 2:34 PM

Try:

=SUMPRODUCT((LEFT(RIGHT(D2:D201,5),1))+0>0)*(RIGHT(D2:D201,5),1))+0 &LT; 9)*(H2:H201>14))

PS. Mark, this time with +0.

=SUM((AND((VALUE(LEFT(RIGHT(D2:D201,5),1))>0),(VALUE(LEFT(RIGHT(D2:D201,5),1))&LT;9)))+((H2:H201>14)-(H2:H201="")))

Posted by Aladin Akyurek on July 24, 2001 2:38 PM

I see I left one of your LEFTs out. It should be:

=SUMPRODUCT((LEFT(RIGHT(D2:D201,5),1))+0>0)*(LEFT(RIGHT(D2:D201,5),1))+0 &LT; 9)*(H2:H201>14))