lookup and count
I have the following type data and need to first lookup by the dealer # and
then count the model number but need to use wild cards. I can use =COUNTIF(DATA!A2:G1034,"8EC*E*") for all dealers combined but can seem to cound by dealer? Please help! col B col C 4993059 4F25NL 4993059 4F55NL 4993059 8EC5EC 4993059 8EC5EL 4993059 8ECXE9 4993059 8ECXEH 4993059 8EDXEL 4993059 8N35BL 4993059 8PA51L 4993059 8PA52X 4993250 4BH57Z 4993250 4BH57Z 4993250 4E251L 4993250 4E251L 4993250 4E251L 4993250 4F255L 4993250 4F255L 4993250 4F255L 4993250 4F255L 4993250 4F255L 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F55NL 4993250 4F55NL 4993250 4F55NL 4993250 4F55NL 4993250 4F55NL 4993250 4F55NL 4993250 4F55NL 4993250 4F55NL 4993250 4F55NL 4993250 8EC549 4993250 8EC549 4993250 8EC5E9 4993250 8EC5EC |
lookup and count
Hi!
Use 2 cells to hold the criteria: D1 = 4993059 E1 = 8EC*E* (might be better to use 8EC?E? since the ? wildcard represents only a single character) =SUMPRODUCT(--(B1:B47=D1),--(ISNUMBER(SEARCH(E1,C1:C47)))) Biff "DRvfr" wrote in message ... I have the following type data and need to first lookup by the dealer # and then count the model number but need to use wild cards. I can use =COUNTIF(DATA!A2:G1034,"8EC*E*") for all dealers combined but can seem to cound by dealer? Please help! col B col C 4993059 4F25NL 4993059 4F55NL 4993059 8EC5EC 4993059 8EC5EL 4993059 8ECXE9 4993059 8ECXEH 4993059 8EDXEL 4993059 8N35BL 4993059 8PA51L 4993059 8PA52X 4993250 4BH57Z 4993250 4BH57Z 4993250 4E251L 4993250 4E251L 4993250 4E251L 4993250 4F255L 4993250 4F255L 4993250 4F255L 4993250 4F255L 4993250 4F255L 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F25NL 4993250 4F55NL 4993250 4F55NL 4993250 4F55NL 4993250 4F55NL 4993250 4F55NL 4993250 4F55NL 4993250 4F55NL 4993250 4F55NL 4993250 4F55NL 4993250 8EC549 4993250 8EC549 4993250 8EC5E9 4993250 8EC5EC |
All times are GMT +1. The time now is 01:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com