MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Counting License Plates

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.


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


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


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


PS. Mark, this time with +0.


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 < 9)*(H2:H201>14))


Posted by Mark W. on July 24, 2001 2:39 PM

How about...

{=SUM((MID(D2:D201,2,1)<"A")*(H2:H201>14))} =SUM((AND((VALUE(LEFT(RIGHT(D2:D201,5),1))>0),(VALUE(LEFT(RIGHT(D2:D201,5),1))<9)))+((H2:H201>14)-(H2:H201="")))