Counting letter ranges

[Carter]

Greetings,

I have a list of company names that I need to tally a number for based on
the first two letters of the name over a range. For instance how many
companies begin with the letters "HI-MF". I have used the formula
=COUNTIF(A:A,"HI*") to get the first part but I do not know how to expand
this formula over a range of letters.

Any assistance would be very much appreciated... Best Regards,

 

[Bob Phillips]

Try this

=SUMPRODUCT(--(LEFT(A2:A20,2)>="HI"),--(LEFT(A2:A20,2)<="MF"))

HTH

Bob

 

[Ron Rosenfeld]

Try this

=SUMPRODUCT(--(LEFT(A2:A20,2)>="HI"),--(LEFT(A2:A20,2)<="MF"))

HTH

Bob

Modification to eliminate counting entries like:

A2: M

=SUMPRODUCT(--(LEFT(rng,2)>="HI"),--(LEFT(rng,2)<="MF"),--(LEN(rng)>=2))

--ron

 

[Bob Phillips]

Good point!

Bob

Modification to eliminate counting entries like:

A2: M

=SUMPRODUCT(--(LEFT(rng,2)>="HI"),--(LEFT(rng,2)<="MF"),--(LEN(rng)>=2))

--ron

 

[Carter]

Both formulas work great! Many thanks!