Find the ammount of times certain text appears within a text

Frans Scheepers

Board Regular
Joined
Apr 21, 2006
Messages
127
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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))))
 

Forum statistics

Threads
1,141,144
Messages
5,704,541
Members
421,353
Latest member
jekoxien15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top