Counting cells that contain a certain bit of text without duplicates

Question

I need to count the number of cells in a column that contain a certain bit of text. Actually, I will be doing multiple of these counts, and I need to remove the duplicates.

Example info:
Lala
Lerly PTY LTD
Trang AS TTE
Trerek AS TTE
PRING PTY LTD AS TTE
Treps PTY LTD
Liang AS TTE
Praderpis PTY LTD AS TTE

I need to count the amount of times "AS TTE" shows up, and also the amount of times "PTY LTD" shows up. However, I only want to count the cells that contain both "AS TTE" and "PTY LTD" once.

I have this so far:

(COUNTIF(F4:F1000,"*AS TTE*") + COUNTIF(F4:F1000, "*PTY LTD*"))

However this counts duplicates.

edit: I should clarify, my uses for this are much more complicated than what I have listed, so it isn't as easy as simply subtracting another COUNTIF that has the both of them. I have many other COUNTIFs I will be doing.

Answer 1

Do the ones having "AS TTE" and "PTY LTD" always have them in the order "PTY LTD AS TTE"?

If so, you could add:

COUNTIF(F4:F1000,"*PTY LTD AS TTE*")

Your net formula will be:

(COUNTIF(F4:F1000,"*AS TTE*") + COUNTIF(F4:F1000, "*PTY LTD*") - COUNTIF(F4:F1000,"*PTY LTD AS TTE*"))

EDIT: If you cannot simply do another COUNTIF, you could perhaps add column where you have the formula:

=IF(AND(ISERROR(FIND("AS TTE",E4)),ISERROR(FIND("PTY LTD",E4))),0,1)

This will insert 1 if "AS TTE" or "PTY LTD" is in cell F4, and 0 if not. Drag the formula down and add the column to get the total.

Answer 2

You could add three additional columns to your input data table:

          A
 1 | Lerly PTY LTD | =COUNTIF(A1,"*PTY LTD*") | =COUNTIF(A1,"*AS TTE*") | =B1+C1-B1*C1
 2 | Trang AS TTE  | =COUNTIF(A2,"*PTY LTD*") | =COUNTIF(A2,"*AS TTE*") | =B2+C2-B2*C2
 ...

and then sum up the third additional column, which will have a one in it for each row in which one of the criteria is met.

Answer 3

Can you add a column? If you can, add a (maybe hidden) column, say B, with formulas like

=if(and(iserror(find("PTY LTD",A1)),iserror(find("PTY LTD",A1))),0,1)

(assuming data in in column A) then just sum that column. The formula can obviously include as many conditions as you like. Note that what I'm actually doing is looking for lines that include neither term and omitting them from the sum.

Answer 4

Depending on just how complicated you need the count to be you may be better using a custom function, the below could be expanded to measure a number of criteria but as it is will count any cell that contains "AS TTE" OR "PTY LTD" but count each cell as 1:

Public Function CountIfExt(Rng As Range, str1 As String, str2 As String) As Long

Dim c As Range
Dim x As Long

For Each c In Rng
    If InStr(c.Value, str1) > 0 Or InStr(c.Value, str2) > 0 Then
        x = x + 1
    End If
Next c

CountIfExt = x

End Function

Changing the 'Or' to 'And' would count cells that contained both strings but to check that the cell contained both only once, ie exclude a cell that had 'Trang AS TTE PTY LTD AS TTE' then: (having re-read your question I don't think this is what you were after but to demonstrate how you can expand on the function...)

Public Function CountIfExt(Rng As Range, str1 As String, str2 As String) As Long

Dim c As Range
Dim x As Long
Dim lstr1 As Long, lstr2 As Long

For Each c In Rng
    lstr1 = InStr(c.Value, str1)
    lstr2 = InStr(c.Value, str2)
    If lstr1 > 0 And lstr2 > 0 Then
        If InStr(lstr1 + 1, c.Value, str1) = 0 _
                And InStr(lstr2 + 1, c.Value, str2) = 0 Then
            x = x + 1
        End If
    End If
Next c

CountIfExt = x

End Function

Hopefully this gives you enough to get where you need it to be.