Count number of cells containing specific text

Hi,

I need to count the number of cells that contains a part of a text.

Is there any wildcart symbol I could use?

for example

abcd

abfd

abpd

jged

In this case I need a formule that count the cells with the text starting with the letters ab.

/Michelangelo

Find more posts tagged with

Formulas

I don't think there is a wildcard, but you could use

=COUNTIF(LEFT(text1, 2), "ab")

Christine

I see where Christine is going with her COUNTIF, but I couldn't quite get it to work.

I added a column (a checkbox column) with this formula:

=IF(LEFT([Primary Column]9, 2) = "ab", 1, 0)

The check box is checked if the [Primary Column] starts with "ab".

If your data is under a parent hierarchy, then this at the parent row

=COUNTIF(CHILDREN(), 1) + ""

will get you a count (in text format) of the number of 'ab' found in the children.

I convert to text because checkboxes won't take numbers greater than 1.

Or change the column to a text/number column and use a SUM on the CHILDREN instead.

If your data is NOT in a hierarchy, then

=COUNTIF([Primary Column]:[Primary Column], 1) + ""

If you need to use the number in a formula, convert it back using VALUE.

Craig

Oh, and if you want to find any instance of "ab" (jabc) not jus the first two letters, try FIND.