How to sum cells with a certain letters sequence

M

Michal

How to sum cells which contains the same sequence of letters? i mean I have a
column with following texts: abcd, bcde, cdef. How to sum cells which contain
"bc" sequence?
 
F

Fanatik

The formula is

=SUMIF(A:A,"*bc*",B:B)

Where column A contains the text amd column B contains the cells you wish to
sum.

Note if you prefer you can enter ranges instead of columns.
 
B

B. R.Ramachandran

Hi,

Do you mean, how to 'count' cells that contain a certain sequence of letters
- i.e., how many strings contain, e.g., "bc" in them? If yes,
= COUNTIF(A2:A100,"=*bc*")
Chanage the range to suit to your needs.

A convenient variation would be,
=COUNTIF(A2:A100,"=*"&$B$2&"*"), where cell B2 will contain the substring
(e.g., bc) you are looking for in the strings. Now you can change the
contents of B2 without having to modify the formula every time.

Regards,
B. R. Ramachandran
 
Top