Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Instances
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Instances
Use Countif, as in:
=countif(sheet2!a1:g4,"widget 1") Regards, Fred "Chad Wodskow" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Instances
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) -- If this helps, please remember to click yes. "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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Instances
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Instances
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,")) "JLatham" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count instances of a reference? | Excel Worksheet Functions | |||
count different instances in a list | Excel Discussion (Misc queries) | |||
Count Instances of value in two columns | Excel Worksheet Functions | |||
Count the number of Instances | Excel Discussion (Misc queries) | |||
Count Instances | Excel Discussion (Misc queries) |