Counting numbers withing a mixed text/number cell

  • Thread starter Thread starter Jared Croft
  • Start date Start date
J

Jared Croft

I am trying to count the number of times certain zip codes appear in a
mass mailing list. COUNT or COUNTA would work except for the fact
that the zip codes are not in separate cells - they are in the same cell as
the City and State information.

If there is a way to separate the zip from the rest and then count it that
would be great. If there is a way to count the zip codes while still in the
same cells as the city and state info that would be better.

Jared
 
With your city/state/zip information in cells C2:C36, and the zip code
that you want to count in cell K1, enter the following formula in cell K2:

=SUMPRODUCT(ISNUMBER(SEARCH(K1,$C$2:$C$36))*($C$2:$C$36<>""))
 
Or just using the double unary to coerce numeric:-

=SUMPRODUCT(--ISNUMBER(SEARCH(K1,$C$2:$C$36)))
 
=countif(A:A,"*12345*")

--
Regards,
Tom Ogilvy


I am trying to count the number of times certain zip codes appear in a
mass mailing list. COUNT or COUNTA would work except for the fact
that the zip codes are not in separate cells - they are in the same cell as
the City and State information.

If there is a way to separate the zip from the rest and then count it that
would be great. If there is a way to count the zip codes while still in the
same cells as the city and state info that would be better.

Jared
 
Back
Top