It is unclear what your data looks like.

Are you actually trying to find the number of times a phrase such as "Widget
1" appears within a longer phrase like "Line 1. Widget 1, Widget 2, Widget 3,
Widget 1, Widget 4, Widget 3, Widget 6"

or, for those sample entries you show, are the entries actually in separate
columns?

I don't know how long either list is, so I don't know if this is a practical
solution or not. But it is based on an assumption that you sample entries
are single text strings and not in separate columns.

=SUM((LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5,"Widget 1","")))/LEN("Widget 1"))
Enter that as an array formula, substituting the actual range for your "Line
#. ..." entries as required. To enter as an array formula, instead of just
pressing [Enter] to end the entry, press [Shift]+[Ctrl]+[Enter]. Then the
formula should look like
{=SUM((LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5,"Widget 1","")))/LEN("Widget 1"))} in
the formula bar.

You can reference an entry on the other sheet instead of the literal "Widget
1" for which ever item you want to count:
=SUM((LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5,'Product List'!B9,"")))/LEN('Product
List'!B9))
assuming B9 on the 'Product List' sheet holds "Widget 1".

Hope this helps.






"Chad Wodskow" wrote:

I am trying to count the number of instances a list of products appear on a
report. The list of products is in a range on a seperate sheet.

Line 1. Widget 1, Widget 2, Widget 3, Widget 1, Widget 4, Widget 3, Widget 6
Line 2. Widget 1, Widget 2, Widget 3, Widget 1, Widget 4, Widget 3, Widget 6
Line 3. Widget 1, Widget 2, Widget 3, Widget 1, Widget 4, Widget 3, Widget 6
Line 4. Widget 1, Widget 2, Widget 3, Widget 1, Widget 4, Widget 3, Widget 6

Product Sheet
Widget 1
Widget 3


Total 16