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.
=SUM((AND((VALUE(LEFT(RIGHT(D2:D201,5),1))>0),(VALUE(LEFT(RIGHT(D2:D201,5),1))<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 < 9)*(H2:H201>14))
Aladin
PS. Mark, this time with +0.
=SUM((AND((VALUE(LEFT(RIGHT(D2:D201,5),1))>0),(VALUE(LEFT(RIGHT(D2:D201,5),1))<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 < 9)*(H2:H201>14))
Aladin
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="")))
