Excel Magic Trick 290: Count Codes From Column of Text Strings

3,880 views · Published 16 March 2009 · 6:11 · Indexed 10 November 2026

Channel: excelisfun · 2009 · Science & Technology

Watch on YouTube

Download Excel Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT285-294.xls
Download Excel Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT285-294Finished.xls
Full Page With All File Links: https://people.highline.edu/mgirvin/excelisfun.htm

See how to count codes from a column of text strings that hah multiple codes. For example: If we are counting PCP in this list: {"PCP, TP, N, N, PA";"N, N, PA";"PCP, N, PA";"TP, N, NP"} we would want to get 2.
See a formula that counts that uses the SUMPRODUCT, LEN and SUBSTITUTE functions.
LEN & SUBSTITUTE functions Counting Tricks

More from this channel

• 3:51 YouTubersLoveExcel#19: Steady Growth Cash Flow Formula

  excelisfun

  3,557 views · 23 May 2008

• 3:44 YouTubersLoveExcel#26: Text To Column Trouble

  excelisfun

  3,596 views · 30 May 2008

• 3:41 YouTubersLoveExcel#26.2: Formulas do not like TEXT Time

  excelisfun

  2,016 views · 6 June 2008

• 3:30 YouTubersLoveExcel#34: Hide Columns Copy Problem

  excelisfun

  3,877 views · 11 June 2008

• 9:27 YouTubersLoveExcel#31: Dates, Cash Flow & Logic

  excelisfun

  4,909 views · 11 June 2008

• 1:57 YouTubersLoveExcel#36: VLOOKUP increase based on Rating

  excelisfun

  3,053 views · 16 June 2008

• 1:39 YTLE#60: Hide Columns by Grouping

  excelisfun

  4,807 views · 18 July 2008

• 5:07 YTLE#63: Array Formula: Vendor Max Rating Min Cost

  excelisfun

  2,134 views · 23 July 2008