count a word twice if it appears in a cell twice

System

This discussion was created from comments split from: How to use a COUNTIF formula to determine how many times a word appears in a multi-select column?.

KPH

Does anyone know if it is possible to extend this to count a word twice if it appears in a cell twice?

For Example:

Column: Fruit

Cell 1: Apples, Pears Bananas

Cell 2: Apples, Pears, Apples

Cell 3: Apples, Oranges, Bananas

Summary:

Apples: 4

Pears: 2

Bananas: 2

Oranges: 1

Leibel S

@KPH

The below is an example formula:

=COUNTIF(Fruits:Fruits, CONTAINS("Apples", @cell)) + COUNTIF(Fruits:Fruits, CONTAINS("SecondApples", SUBSTITUTE(@cell, "Apples", "SecondApples", 2)))