lookup and count

[DRvfr]

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

[Biff]

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" <DRvfr@discussions.microsoft.com> wrote in message
news:D81D2F8D-6270-42A3-AB37-9A0CA5709710@microsoft.com...
>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
>