Count the number of first letters derived from words in a row

I can use the following formula to accomplish what I need, but it is not very efficient, as I have to type a formula for every letter from A-Z, whether that letter is even used.

Please Login or Register  to view this content.

I would prefer to do it dynamically, so it would only count the letters that are present. The count of the letters are derived from the first letter of every word within a row.

So, if cells A1, B1, C1 equal blue, green, black, respectively, then the count would be G=1 and B=2. That means one word was found that starts with the letter 'G' and two words were found that start with the letter 'B'.

The range is G4:BB4 and each roll down to G126:BB126. I assume this will require an array formula.


Hope that makes sense!

Here's one option:

=CHAR(ROWS($1:65))&"="&SUMPRODUCT(--(UPPER(LEFT(G$126:BB$126))=CHAR(ROWS($1:65))))

Drag the formula down the column.

Sorry, I was in a rush when I sent that.

The formula was built to work for one row and be dragged down from A=# to Z=#.

If you have multiple rows and want to drag the formula to the right, you can use this instead:
BD4 =CHAR(COLUMNS($A:BM))&"="&SUMPRODUCT(--(UPPER(LEFT($G4:$BB4))=CHAR(COLUMNS($A:BM))))

Drag the formula to the right.

This will give you A to Z with the counts each in a different cell.

Originally Posted by 63falcondude

Sorry, I was in a rush when I sent that.

The formula was built to work for one row and be dragged down from A=# to Z=#.

If you have multiple rows and want to drag the formula to the right, you can use this instead:
BD4 =CHAR(COLUMNS($A:BM))&"="&SUMPRODUCT(--(UPPER(LEFT($G4:$BB4))=CHAR(COLUMNS($A:BM))))

Drag the formula to the right.

This will give you A to Z with the counts each in a different cell.

I have a follow up question, if you don't mind.

I really need to sort the results based on the count of each letter found since many of the letters count's equal 0 and are not essential. So basically all the letters that equal zero are sorted to the right or further out, while the letters with a higher count are displayed to the left. Not sure if it's possible to sort them as is, since each cell result is labeled as an example: A=3, B=1, C=2, D=0. I would assume I need them to show the count first followed by the "=" then letter, like 3=A, 1=B, 2=C, 0=D. Then it would be easier to sort them I assume.

Well, it looks like you can't sort horizontally in Excel.

It would be great if I could convert the range of results, which is G1:BB1, to a dropdown list and have the results sorted as I mentioned earlier, with the higher counts at the top.

Originally Posted by AliGW

Yes, you can sort horizontally, but this is a very different question, so please start a new thread for it. It is to do with sorting, not counting, so your new thread will need an appropriate title. Thanks.

Ok, I figured it out after some additional testing. I will mark this thread as Solved.