Don't make me count them all

March 4, 2009 11:45 AM

Excel-Fu Filter: I have a list of some 2k titles in a single column in Excel. How do I get a count of every title beginning with a given letter?

Long story short: Due to ridiculous application limitations, I have to split my bug-tracker into 3 roughly equal-sized instances. The split is going to be alphabetical by title.

I have a list in a single column of every title. I want to see the alphabetical distribution by title, that is, how many titles start with A, etc. How do I do this without counting them myself?

posted by bluejayway to Computers & Internet (9 answers total)

Sort the list into alphabetic order, then find the first and last entries with that starting letter and look at their cell numbers.
posted by Chocolate Pickle at 11:48 AM on March 4, 2009

I should not answer these things as soon as I wake up. I think = left(a1, 1)
posted by b33j at 12:00 PM on March 4, 2009

Best answer: Perhaps not the most elegant solution (curious to see who can do this in one cell), but I would first insert a new column (let's call it new column B). Then enter this formula in cell B2 (assuming your title data begins in cell a2): =left(a2,1). This will return the first letter of the title. Copy and paste this formula all the way down.

Next, in a blank cell anywhere on the sheet type: =COUNTIF($B:$B,"a"). This will return the number beginning with an A. Copy and paste the formula, substituting the "a" with whichever letter you want to take a count for.

Be wary of titles beginning with "The". Depending on how you want to deal with those, that might warrant a separate AskMe :^)
posted by Gee Your Hair Smells Terrific at 12:20 PM on March 4, 2009 [1 favorite]

Sort in a-z order and highlight cells that start with A - there should be a "Count: x" on the bottom right of your excel window. (Under the scroll bar.)

Or =LEFT(A1:A30, 1) like b33j suggested. It means LEFT reads the characters in a text box A1:A30 is the cell range, adjust to your total number of cells so it would be A1:A500, and the 1 specifies how many characters to read. 1 means the first, only.
posted by kirstk at 12:27 PM on March 4, 2009

Create a new column called "First Letter of Title". Enter the formula =LEFT($A:$A, 1), replacing the A with your title column, in one of the cells. Drag it or copy and paste to fill the column with that same formula. Click any cell in your First Letter of Title column, then go into Data/Subtotals and hit OK, accepting the defaults (At each change in First Letter of Title, use function Count, etc..) Excel will automatically work out the total for each initial letter and give you a breakdown as well as controls on the left side you can use to collapse each letter's rows.
posted by pocams at 12:34 PM on March 4, 2009

Response by poster: Thanks for all the answers, folks. I just did a find and replace for all the "The" titles and followed the advice of those who suggested =left(...). Worked great.
posted by bluejayway at 1:14 PM on March 4, 2009

Be wary of titles beginning with "The". Depending on how you want to deal with those, that might warrant a separate AskMe :^)

For an old askme on this subject, see here. It also deals with titles that start with "A" and "An".
posted by inigo2 at 1:59 PM on March 4, 2009

You probably should have done a find/replace for "The " not "The" (mind the space!), or "Theoretical..." becomes "oretical..."
posted by misterbrandt at 7:13 PM on March 4, 2009

About the first accepted answer, you can use wildcards with COUNTIF. For example, try =COUNTIF($A:$A, "a*") to see how many records in column A start with the letter A.

See "Use the COUNTIFS function in Excel 2007 to analyze data" which has the example "How many songs were sung by singers whose last names begin with S?"

But pocam's solution seems easier.
posted by Yogurt at 11:40 AM on March 5, 2009 [1 favorite]