Array Problem

[olsoniv]

I am trying to use an array to count the number of instances for a sales person and the area they sold in.

The formula looks like this:
{=SUM((Salesperson=SpecificSalesperson)*(Area=SpecificArea)*(1))}

Salesperson is the range of Cells where all names of sales people are.
Area is the range of cells where all areas for same records are.

This works in my test bed where there are only 10 entries. My actual data has about 14000 entries. When I try to use this I get a #NUM back. Why is this working in my test bed but not in my actual data sheet?

[mudraker]

I beleive it is because there is a limit as to how many rows you can run an array formula on.

I have not used array formulas for years. When I did use them I remember running into similar problems once the number of rows got up around 1,000.

Have a look at
http://www.ozgrid.com/Excel/arrays.htm
http://www.ozgrid.com/excel-add-ins/array-formulas.htm

[daddylonglegs]

I am trying to use an array to count the number of instances for a sales person and the area they sold in.

The formula looks like this:
{=SUM((Salesperson=SpecificSalesperson)*(Area=SpecificArea)*(1))}

Salesperson is the range of Cells where all names of sales people are.
Area is the range of cells where all areas for same records are.

This works in my test bed where there are only 10 entries. My actual data has about 14000 entries. When I try to use this I get a #NUM back. Why is this working in my test bed but not in my actual data sheet?

I don't believe there's any limit, are you sure you don't have a #NUM! error somewhere in your data?

btw you don't need the *(1) part, this is sufficient

{=SUM((Salesperson=SpecificSalesperson)*(Area=SpecificArea))}

Perhaps you can try SUMPRODUCT, which doesn't need to be "array entered" but if you do have errors in the data you'll get the same result......

=SUMPRODUCT(--(Salesperson=SpecificSalesperson),--(Area=SpecificArea))

[olsoniv]

Thanks for the inputs....I will try sumproduct and see if I can't get that to work. I don't have any #num's in my data as it is the raw data.