Average a column containing numbers and text
in [Functions]
|
Prev: IF STATEMENT
Next: Ranking - Not Sorting Properly.
From: Malcolm on 21 May 2010 19:15 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
From: Dave Peterson on 21 May 2010 19:19 =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. Malcolm wrote: > > 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 -- Dave Peterson
From: FSt1 on 21 May 2010 19:44 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 "Malcolm" wrote: > 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
From: Malcolm on 21 May 2010 20:12 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 "FSt1" wrote: > 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 > > > > "Malcolm" wrote: > > > 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
From: FSt1 on 21 May 2010 20:53
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 "Malcolm" wrote: > 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 > > > "FSt1" wrote: > > > 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 > > > > > > > > "Malcolm" wrote: > > > > > 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 |