Count number of occurrences of list of characters in a single string

[VorLag]

I am trying to figure out a good way to do this... So, let's say I want to count the number of vowels in a word... how would I do this?

I don't want to have to do a separate formula for each vowel and I can't seem to get an array formula to work. Does anyone have any ideas?

The list will look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Word List[/TD]
[TD]# Vowels[/TD]
[TD][/TD]
[TD][/TD]
[TD]Vowels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Cupcake[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Argyle[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Liquidator[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Octopus[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Grass[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Asala42]

I'm sure there are other methods, but I'd probably just use a LEN/Substitute combination.

For example calculating the number of times "a" shows up in a string:
=LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"a",""))

Calculating the number of times any vowel shows up in a string:
=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A2),"a",""),"e",""),"i",""),"o",""),"u",""))

 

[Rick Rothstein]

Another way...

=SUMPRODUCT(--(MID(A1,ROW(1:999),1)={"a","e","i","o","u"}))

 

[VorLag]

Another way...

=SUMPRODUCT(--(MID(A1,ROW(1:999),1)={"a","e","i","o","u"}))

Completely forgot about this as a possibility. It works like a charm! Thanks!!

 

[Marcelo Branco]

As the formula should be copied down i suggest a slight modification

=SUMPRODUCT(--(MID(A1,ROW($1:$999),1)={"a","e","i","o","u"}))

M.

 

[Rick Rothstein]

As the formula should be copied down i suggest a slight modification

=SUMPRODUCT(--(MID(A1,ROW($1:$999),1)={"a","e","i","o","u"}))

Good point! I was so intent on getting the formula to work that I forgot it would be copied down. Thanks for picking up on that.