Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help! With SUMPRODUCT
I am trying to total the occurrences of comma delimited numbers in a column.
See original post below: I have a column which has a variable number of comma delimited values in the range of 11 to 56 in each cell. Is there a method or macro to count the occurrences of each number in the column and either output the results to a file or another worksheet or the same worksheet? Example: A1: 23,40,52,31, A2: 42,14, A3: 56, A4: 27,43,19, etc. What I am doing now is coping the column to Word, closing the margins to 3 characters, coping that result to an clean worksheet, sorting the new column ascending and printing the results and counting the occurrences manually. This is getting tedious as the column grows in length. Can anyone help me or point me to a solution? Biff replied with a formula using SUMPRODUCT. See his reply below: Hi! Assume the numbers are in the range A1:A10. In C1 enter 11. In D1 enter this formula: =SUMPRODUCT(LEN(A$1:A$10)-LEN(SUBSTITUTE(A$1:A$10,C1,"")))/2 Select both C1 and D1 and drag copy down to row 46. Biff For the life of me I cannot get this to work. When I enter his formula (exactly as written in a test worksheet), I get an error #NAME? Can anyone please help. Thanks a million, Glynn .. gfurr1 at nc dot rr dot com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help! With SUMPRODUCT
Hi!
The only reason that #NAME? should be the result is if one of the functions was misspelled? Here's a sample file that demonstrates this: count_instances.xls 14kb http://s54.yousendit.com/d.aspx?id=3...31HIB756ZT0TN5 Biff "Glynn Furr" wrote in message ... I am trying to total the occurrences of comma delimited numbers in a column. See original post below: I have a column which has a variable number of comma delimited values in the range of 11 to 56 in each cell. Is there a method or macro to count the occurrences of each number in the column and either output the results to a file or another worksheet or the same worksheet? Example: A1: 23,40,52,31, A2: 42,14, A3: 56, A4: 27,43,19, etc. What I am doing now is coping the column to Word, closing the margins to 3 characters, coping that result to an clean worksheet, sorting the new column ascending and printing the results and counting the occurrences manually. This is getting tedious as the column grows in length. Can anyone help me or point me to a solution? Biff replied with a formula using SUMPRODUCT. See his reply below: Hi! Assume the numbers are in the range A1:A10. In C1 enter 11. In D1 enter this formula: =SUMPRODUCT(LEN(A$1:A$10)-LEN(SUBSTITUTE(A$1:A$10,C1,"")))/2 Select both C1 and D1 and drag copy down to row 46. Biff For the life of me I cannot get this to work. When I enter his formula (exactly as written in a test worksheet), I get an error #NAME? Can anyone please help. Thanks a million, Glynn .. gfurr1 at nc dot rr dot com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help! With SUMPRODUCT
Hi,
Is Sumproduct() really needed ... Here is the formula I am using : =LEN(A1)-LEN(SUBSTITUTE(A1,",","")) HTH Cheers Carim |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help! With SUMPRODUCT
I think you may have misunderstood what the OP wants to do.
Count the instances a number from 11 to 56 appears in a range of cells. Is Sumproduct() really needed ... You could use SUM and array enter the formula. Biff "Carim" wrote in message oups.com... Hi, Is Sumproduct() really needed ... Here is the formula I am using : =LEN(A1)-LEN(SUBSTITUTE(A1,",","")) HTH Cheers Carim |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help! With SUMPRODUCT
Or use
Data Text to Columns Delimited Comma Add your own row and column headers. Then use Data Pivot Table Multiple Consolidatation Ranges. In Pivot Table Wizard Layout, drag the Row and Column button off the diagram and place the Value button where the Row button used to be. Again, drag the Value button to the Data area and right click it to select Count of Value. In Pivot Table Wizard Options Uncheck Grand Totals |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
Need help with sumproduct & dynamic ranges | Excel Worksheet Functions | |||
Need help with sumproduct and dynamic ranges | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |