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:

	A	B	C	D	E	F	G	H	I
1	Word List	# Vowels			Vowels
2	Cupcake				A
3	Argyle				E
4	Liquidator				I
5	Octopus				O
6	Grass				U
7					Y

<tbody>
</tbody>

 

[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.