Help! With SUMPRODUCT

[Glynn Furr]

I am trying to total the occurrences of comma delimited numbers in a column.
See original post below:

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

 

[Biff]

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=3UJ5KPDQDO6Y31HIB756ZT0TN5

Biff

 

[Carim]

Hi,

Is Sumproduct() really needed ...
Here is the formula I am using :

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

HTH
Cheers
Carim

 

[Biff]

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

 

[Herbert Seidenberg]

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