| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average a column containing numbers and text
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average a column containing numbers and text
=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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average a column containing numbers and text
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average a column containing numbers and text
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average a column containing numbers and text
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average a column containing numbers and text
opps. that should be
=COUNTIF(B4:B22,"s")+COUNTIF(H4:H21,"s") sorry about that. regards FSt1 "FSt1" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average a column containing numbers and text
Fst1, Hi, Dude, you are the best! Your solution worked slick! A thousand and one thank yous. Malcolm "FSt1" wrote: 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 |
|
#8
|
|||
|
|||
Quote:
sumproduct can also work. however everybody is happy. all happies
__________________
Thanks Bala |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do i average the 3 max numbers in a column? | Excel Worksheet Functions | |||
| Average highest 16 numbers on a column of 32 numbers | Excel Worksheet Functions | |||
| Sum/average numbers in column A dependant on value in column B | Excel Worksheet Functions | |||
| Average first 3 numbers in column e.g. 130 in 130/82 | New Users to Excel | |||
| Average of numbers in column between to other numbers | Excel Discussion (Misc queries) | |||
Linear Mode
