Excel COUNTIF Value Not displayed

Question

I am using COUNTIF to determine the number of cells that contain a string of text characters.

The string I am searching for is: "* UNKNOWN * <Seg 9: 0 feet from [South-West  Corner  (Pole#29)]>"

COUNTIF returns zero, when I count 12 manually.

I have tried using "~" before the "*".

If I search for "* UNKNOWN *" the Function Arguments window shows the Formula Result as 204, (204 may be a correct value) but that value displays as zero after I click OK. I am looking to count a more specific text string.

If I search for "* UNKNOWN * <Seg 9: 0" the Function Arguments window shows 0.

How do I successfully count the number of times a strings of text characters show in a spreadsheet?

Answer 1

Hi,

A couple of samples of the string would have helped but this should work.

=COUNTIF(A1:A20,"*UNKNOWN * <Seg 9: 0 feet from [South-West  Corner  (Pole#29)]>*")

Answer 2

Hi,

Counting the visible rows in a filtered range is straightforward and we can use the formula below.

In the formula column C is the column that contains the text string we're looking for so change that to suit keeping all references to it the same number of rows..

Note we don't use the wildcard characters (*) in this formula , we find the substring by using SEARCH

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C2:C15,ROW(C2:C15)-MIN(ROW(C2:C15)),,1))*(ISNUMBER(SEARCH("the greatUNKNOWN * <Seg 9: 0 feet from [South-West  Corner  (Pole#29)]>",C2:C15))))

Answer 3

Thank you, that clued me into the missing component.

When I originally selected the column, the function inserted G:G, without the row numbers.

Once I inserted the row numbers, it worked well. Thank you. 

=COUNTIF(G:G,"* UNKNOWN * <Seg 9: 0 feet from [South-West  Corner  (Pole#29)]>")

to

=COUNTIF(G1:G3575,"* UNKNOWN * <Seg 9: 0 feet from [South-West  Corner  (Pole#29)]>")

Now, if I could only count the non-filtered rows without deleting the rows from the previous period.