Count Instances

[Chad Wodskow]

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

 

[Fred Smith]

Use Countif, as in:
=countif(sheet2!a1:g4,"widget 1")

Regards,
Fred

 

[Paul C]

ingGJust count for each

=COUNTIF(Sheet1!A1:G4,"Widget 1")+COUNTIF(Sheet1!A1:G4,"Widget 3")

Of course you can reference a cell contain the values Widget 1 and Widget 3
instead

=COUNTIF(Sheet1!A1:G4,A1)+COUNTIF(Sheet1!A1:G4,A2)

 

[JLatham]

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.

 

[JLatham]

Use some caution with this method, remember that when it goes looking for
"Widget 1" that it's going to treat Widget 1, Widget 11, Widget 101, etc.
just as if it had found Widget 1. So you may want to:

#1 - add a comma at the end of each long text string, so
Line 1. Widget 1, Widget 2, Widget 3, Widget 1, Widget 4, Widget 3, Widget 6
would become
Line 1. Widget 1, Widget 2, Widget 3, Widget 1, Widget 4, Widget 3, Widget 6,

then search for products and include the commas part of the
search/substitution, as:
=SUM((LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5,"Widget 1,","")))/LEN("Widget 1,"))

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.

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