» Counting the Number of Times a Specified Substring Appears within a Range of Strings
CATEGORY - Excel Counting
VERSION - All Microsoft Excel Versions
Counting the total number of times each of the substrings listed in column B appears within any of the strings in column A.
Solution:
Use the SUMPRODUCT, LEN, and SUBSTITUTE functions as shown in the following formula:
=SUMPRODUCT(LEN($A$2:$A$6)- LEN(SUBSTITUTE($A$2:$A$6,B2," ")))/LEN(B2)
Book Store:
Recommended Books:
- Excel 2002 Power Programming with VBA
- Marketing Plans That Work, Targeting Growth and Profitability
- Marketing Planning for Services
- Final Accounting: Ambition, Greed and the Fall of Arthur Andersen
- Harry Potter and the Order of the Phoenix (Book 5)
- Preparing the Marketing Plan (Ama Marketing Toolbox Series New Edition)
No comments have been submitted.
