Find the ammount of times certain text appears within a text

[Frans Scheepers]

Good day all

I would like to know if there is a way to use a formula to identify how many times the text strings "CEL" and "DPP" appears together in the the same text string for an entire column in a spreadsheet.

Keep in mind that I do not want to use the FIND and AND function by copying them down the adjacent column and then count the TRUE values using COUNTIF because the spreadsheet will be to large in size then.

I am looking at anywhere between 500 to 15 000 entries that must be searched and counted at the end of the day and my other collueges that also use this spreadsheet will not understand the thing of copying and pasting formulas in different cells.

 

[Scott Huish]

You can't use a whole column with SUMPRODUCT but this sample uses 15000 rows:

=SUMPRODUCT(--(ISNUMBER(SEARCH("CEL",A1:A15000))),--(ISNUMBER(SEARCH("DPP",A1:A15000))))

 

[Frans Scheepers]

Thanx Pepper

That worked great