How do I count occurances of a specific character in a range?

V

Viridel

I collect stats for a sports team, and the sheet used tracks Goals (G),
Assists (A) and Shots (I) all in the same cell, with one cell per period.
What I need to be able to do is track the number of times a "G" comes up in a
period for a team, as well as over all the periods of the game for the
specific player. COUNT seems to be all-or-none for the entire cell (match or
doesn't match), while FIND/SEARCH only returns the first incidence of the
character (so if two goals are scored by the same player in the same period,
only the first gets recorded)

So, If I were to use the following data list, what could I put at "the
bottom" in order to give a Goals Total for that period?

AA
GGI
G
IG
A
A

Please & Thanx!
- Jason
 
D

Dave Peterson

To count the number of G's in a single cell:
=len(a1)-len(substitute(a1,"G",""))

to count the number of G's or g's (upper or lower) in a single cell:
=len(a1)-len(substitute(upper(a1),"G",""))

To count the number of G's in a contiguous range:
=sumproduct(len(a1:a10)-len(substitute(a1:a10,"G","")))

To count the number of G's or g's in a contigous range:
=sumproduct(len(a1:a10)-len(substitute(upper(a1:a10),"G","")))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If AND for large range of data 1
Count the number of occurances in a cell 2
What formula do I use for copying cell information between workboo 2
Count occurances in a range. 5
How fix a too long array function 5
Providing a reference to another cell in a different workbook 2
How do I expand a funtion's maximum character length? eg CUBESET 2
Count the times a specific character appears in a cell 3

Top