the sum of cells with letters in them

[alienyde]

Hi,

I am making a list for my dvd's with excel and the way that i did thi
is that I used a column for each letter, column a for movies startin
with a...etc. and what I want to know is the formula that adds up th
tolal number of cells to get a total of how many dvd's I have pe
letter. As well as if I had more to add the total each tim
automatically. The problem is that when you use the sum formula it
always trying to find a number in each cell to add up and if I writ
letters that form a word...the formula does not see numbers so th
total is always 0.

Can someone help me?

Etienn

 

[Paul Sheppard]

Hi,

I am making a list for my dvd's with excel and the way that i did this
is that I used a column for each letter, column a for movies starting
with a...etc. and what I want to know is the formula that adds up the
tolal number of cells to get a total of how many dvd's I have per
letter. As well as if I had more to add the total each time
automatically. The problem is that when you use the sum formula its
always trying to find a number in each cell to add up and if I write
letters that form a word...the formula does not see numbers so the
total is always 0.

Can someone help me?

Etienne

Hi Etienne

Try COUNTA function, this counts the number of cells that are not blank

 

[alienyde]

thanks, that worked

Etienn

 

[Don Guillett]

Examples would be helpful

 

[Dave Peterson]

You could use a formula like:

=countif(a1:a99,"A*")

To get a count of titles that start with the letter A.

You could use a column of formulas like:

=left(a2,1)
and drag down

Then you could sort your list and apply Data|Subtotals and use a function of
count.

(Or use that helper column when you create a pivottable.)