Count each Value from a List of Values in a Column

[Eric G]

I have a list of passages in one column and a list of words or phrases in another column. Then I have a third column reserved for how many times each word or phrase appears in the column with the list of passages.

For example:

In column A (List of passages),
A1: The red fox jumped over the red fence.
A2: The blue chicken clucked at the red fox.
A3: The green frog hopped into the blue pond while the blue chicken was clucking at the red fox.

In column C (List of words or phrases),
C1: red
C2: blue
C3: green
C4: red fox
C5: green frog

In column D (Counts for each respective word or phrase),
D1: 4
D2: 2
D3: 1
D4: 3
D5: 1

What formula or VBA code could be used to determine the counts in column D?

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D
1
2	The red fox jumped over the red fence.		red	4
3	The blue chicken clucked at the red fox.		blue	3
4	The green frog hopped into the blue pond while the blue chicken was clucking at the red fox.		green	1
5			red fox	3
6			green frog	1
7
Summary

Cell Formulas
Range	Formula
D2:D6	D2	=SUMPRODUCT(((LEN($A$2:$A$4)-LEN(SUBSTITUTE($A$2:$A$4,C2,"")))/LEN(C2)))

 

[Eric G]

The only problem with your solution, Fluff, is it shows that I don't know how to count. Looks like I missed a "blue" in my example question. As always, thank you. You're a master.

 

[Fluff]

Glad to help & thanks for the feedback.