How to Total the Number of Words that Start with a Specific Letter

[Modify_inc]

I have a list of random words starting at A4 that can go down to A50, though it changes randomly, so sometimes it could be less than A50, but they always start at A4.

In column (F4:F29), I would like to list the total of words found in (A4:A50) that start with each letter of each word. Of course for any that are zero, I would prefer not to display if possible.

Example:
IF,
A4 = Alpha
A5 = Bravo
A6 = Delta
A7 = Dog
A8 = Elephant
etc..

Then respectively:
F4 = 1A (because only one word was found that started with 'A')
F5 = 1B
F6 = 2D (because only 2 words were found that started with 'D')
F7 = 1E
etc..

Hope that makes sense...

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Modify_inc]

What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks for the suggestion, just updated it.

Office 2016.

I'm curious, would this require an array or am I overthinking it?

 

[Fluff]

Thanks for that, how about

+Fluff v2.xlsm
	A	B	C	D
1
2
3
4	Alpha		A	1
5	Bravo		B	1
6	Delta		D	2
7	Dog		F	1
8	Foxtrot
9
Main

Cell Formulas
Range	Formula
C4:C8	C4	=IFERROR(INDEX(LEFT($A$4:$A$8),AGGREGATE(15,6,(ROW($A$4:$A$8)-ROW($A$4)+1)/(ISNA(MATCH(LEFT($A$4:$A$8),C$3:C3,0))),1)),"")
D4:D8	D4	=IF(C4="","",COUNTIF(A:A,C4&"*"))

 

[Modify_inc]

Thanks for that, how about

That could almost work, I just was hoping to keep it to one column if possible.
I just tried something that seems to work, but it's overkill because it uses all the letters in the alphabet.

In F4:
=COUNTIF($A$4:$A$50,"A*")

This seems to work, but I need a way to tell it to use the letters that are already listed in E4. So essentially I need to make it dynamic.

E4:E29 list the letters with the total number of words that should be displayed in A4:A50 that start with that letter.

Example:
E4: 1=A
E5: 1=B
E6: 2=D
E7: 1=E
etc..

So if I could just get the COUNTIF formula above to reference those letters that are listed between E4:E29 (they're not absolute, they do change).
Isn't there a way to make the COUNTIF function in F4 for the ,"A*") portion of it to be a variable so it can equal whatever Letter is in E4, and then do the same for each other Letter listed between E4:E29?

 

[Fluff]

Maybe

=COUNTIF(A:A,Right(E4)&"*")

 

[Modify_inc]

Maybe

=COUNTIF(A:A,Right(E4)&"*")

I came up with the following, but it didn't work, so I'll try yours and let you know shortly

Here's my attempt:
COUNTIF($A$4:$A$50,LEFT(E4,3)&"*")

Then I couldn't remember if Left() starts counting at 0 or 1, so I tried (E4,2), but that didn't make any difference.

 

[Modify_inc]

Maybe

=COUNTIF(A:A,Right(E4)&"*")

Yours worked!!

I still don't see why mine didn't work, it was very similar to yours, I just used the Left(). Any idea why mine didn't? More curious than anything.

Thanks again for your quick responses, you've been extremely helpful!

 

[Fluff]

if you had "1=A" in E4 then the countif needs to use the last character in the cell, hence you need to use right.

 

[Modify_inc]

if you had "1=A" in E4 then the countif needs to use the last character in the cell, hence you need to use right.

Yeah, I just figured it out after testing it. Using Left(E4,3) doesn't just display the 3rd letter, it shows all the letters up to the 3rd letter. I forgot that!

Thanks again!