how to count number of text entries in a range?

[Lyle Blosser]

I have a spreadsheet with a named range ("Names") that contains a list of business names. I want to find out how many businesses have names starting with the letters specified in an interval. For example, I want to answer a question like:

How many businesses have names that start with letters from A-C?
How many businesses have names that start with AM-AT?
etc.

What is the best way to do this? I tried playing around with COUNTIF, but the only way I could come up with an answer was to combine multiple COUNTIF functions. For example, to look for names that start with A-C, I had to write =COUNTIF(Names,"A*")+COUNTIF(Names,"B*")+COUNTIF(Names,"C*"). This is OK for a short range, but gets unwieldy for longer ranges. For example, if I wanted to look for A-M, I would need 13 terms.

I would also like to be able to specify the begining and end of the range in separate cells (ex: A1="AM", B1="AT") and have the formula use those cells.

Any ideas?

 

[Akashwani]

Hi Lyle,

Welcome to MrExcel.

This may not be the best solution or the one you want, but it may help point you in the right direction. This is a snap shot of 2800+ rows of data (NYSE)...

Excel Workbook
	A	B	C
1	Name	Criteria	Count
2	A. Schulman, Inc.	A	258
3	A.C. Moore Arts & Crafts, Inc.	B	120
4	A123 Systems, Inc.	C	324
5	AAON, Inc.	D	84
6	Aastrom Biosciences, Inc.	E	114
7	ABAXIS, Inc.	F	123
8	Abington Bancorp, Inc.	G	95
9	ABIOMED, Inc.	H	95
10	Abraxas Petroleum Corporation	I	142
11	Acacia Research Corporation	J	30
12	ACADIA Pharmaceuticals Inc.	K	42
13	Accelrys, Inc.	L	92
14	Access National Corporation	M	155
Sheet2

The formula in C2 needs to be copied all the way down your criteria range.

Have a look at the file here....

Lyle.xls

The formula is counting the first letter of each name in the list that matches A, B, C etc. You can change the 1 after Names, and the criteria B2 to something else like =SUMPRODUCT(--(LEFT(Names,2)=D2)) D2 being another criteria, something like this....

Excel Workbook
	D	E
1	Criteria	Count
2	AB	4
3	AC	19
4	AD	13
5	AE	6
6	AF	4
7	AG	3
8	AH	0
9	AI	5
Sheet2

I hope that helps.

Good luck

Ak

 

[Lyle Blosser]

Thanks for that prompt reply! I had not considered SUMPRODUCT, and your solution has definite possibilities. The only downside I see is that I would have to specify individually all of the items within each desired interval -- I am still hoping to discover how to simply specify the beginning and end of the interval. But, in the meantime, I will look at your solution in detail -- and, as you suggested, this may help point me in the direction I need to go. Thanks again.

 

[barry houdini]

You can get a count of A-M inclusive with this formula

=COUNTIF(Names,">=A")-COUNTIF(Names,">=N")

 

[Lyle Blosser]

Sweet! That provided the hint I needed to get my solution in place. My final formula is something like this:

=COUNTIF(Names,">=" & A4)-COUNTIF(Names,">" & B4 & "Z")

Cell A4 contains the beginning of the interval and cell B4 contains the ending of the interval. In order to make sure I pick up all of the cells that begin with the ending cell text, I append the character "Z" as shown.

Thanks for that elegant technique!