Counting letter ranges
 

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,

Counting letter ranges
 

Try this

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

HTH

Bob

"Carter" wrote in message
...
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,

Counting letter ranges
 

On Thu, 21 Jan 2010 12:22:15 -0000, "Bob Phillips"
wrote:

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

Counting letter ranges
 

Good point!

Bob

"Ron Rosenfeld" wrote in message
...
On Thu, 21 Jan 2010 12:22:15 -0000, "Bob Phillips"

wrote:

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

Counting letter ranges
 

Both formulas work great! Many thanks!