Average a column containing numbers and text

  • Thread starter Thread starter Malcolm
  • Start date Start date
M

Malcolm

I need to count text cells that contain the letter S and the text C/O. Can
this be done. By the way this would be two different total cells, one for "S"
and one for "C/O".

Thanks in advance
 
=countif(a:a,"s")
(in any cell not in column A)

Will return the number of cells in column A that contain S (that single
character).

=countif(b1:B99,"c/o")
Will return the number of cells in B1:B99 that contain c/o.
 
hi
asuming that the data is in column A......
=COUNTIF(A2:A15,"*s*")
and
=COUNTIF(A2:A15,"*C/O*")
the above uses the wild card character * in case you have more in the cell
than s or C/O. if not then you don' need the wild card characters.

and you said " Average a column " in the subject so.......
=COUNTIF(A2:A15,"*s*")/COUNTA(A2:A15)

regards
FSt1
 
FSt1, Hi,
I'm sorry, I should have given more details. I'm actually looking for the
totals from 2 columns. Let's say column B from B4:B22 and H4:H21, when I
tried your formula it returned the "you have entered too many arguements for
this function" message. Because I entered the formula
=COUNTIF(B4:B22,H4:H21,"*s*")
So I'm looking for the total number of times "S" appears in the 2 columns
for one total, and the total number of times "C/O" appears in the same two
columns for the 2nd. total. There is nothing in the cells except for the
letter S or the text C/O. By the way I'm using Excel 2007.

Thanks again
 
hi
yes that would be too many arguments. so.... do it this way.

=COUNTIF(B4:22,"s")+COUNTIF(H4:H21,"s")

sometimes you just have to play head games with excel.

regards
FSt1
 
opps. that should be

=COUNTIF(B4:B22,"s")+COUNTIF(H4:H21,"s")

sorry about that.

regards
FSt1
 
Fst1, Hi,
Dude, you are the best! Your solution worked slick! A thousand and one thank
yous.
Malcolm
 
Back
Top