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.