Home |
Search |
Today's Posts |
#1
|
|||
|
|||
if cell starts with characters formula
Hi
I need to count cells in a column starting with certain characters. each cell's data varies in length. I have tried with @countif( but does not work if the cell contains other characters after the "prefix". eg. row 20 cell 5 apples row 21 cell 5 apples red row 22 cell 5 apples green row 23 cell 5 plums green row 23 cell 5 plums purple totals required for apples = 3 (regardless of colour) total required for plums = 2 (regardless of colour) @countif(C20:c30,"plums") gives answer of 1 require answer of 2 @countif(C20:c30,"apples") gives answer of 1 require answer of 3 Thanking you in advance Regards Norman -- Message posted via http://www.officekb.com |
#2
|
|||
|
|||
Hi Norman
use the COUNTIF function e.g. =COUNTIF(C20:C30,"*apples*") Cheers JulieD "Norman Kong via OfficeKB.com" wrote in message ... Hi I need to count cells in a column starting with certain characters. each cell's data varies in length. I have tried with @countif( but does not work if the cell contains other characters after the "prefix". eg. row 20 cell 5 apples row 21 cell 5 apples red row 22 cell 5 apples green row 23 cell 5 plums green row 23 cell 5 plums purple totals required for apples = 3 (regardless of colour) total required for plums = 2 (regardless of colour) @countif(C20:c30,"plums") gives answer of 1 require answer of 2 @countif(C20:c30,"apples") gives answer of 1 require answer of 3 Thanking you in advance Regards Norman -- Message posted via http://www.officekb.com |
#3
|
|||
|
|||
|
#4
|
|||
|
|||
you're welcome - thanks for the feedback
"Norman Kong via OfficeKB.com" wrote in message ... It works !!! Thanks -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a Formula in Cell X modify Cell Y? | Excel Discussion (Misc queries) | |||
How do I fix a formula in a cell? | Excel Discussion (Misc queries) | |||
How do I prevent incorrect formula results appearing in cell? | Excel Worksheet Functions | |||
Formula to return cell contents based on multiple conditions | Excel Worksheet Functions | |||
How do you copy a cell formula down a column without displaying n. | Excel Worksheet Functions |