Sumproduct formula "breaks" when referencing too many cells, why?

[hizzle]

I am working on a large sized spreadsheet called 'the data' (about 4000
rows, columns to HJ). A2:A4000 contains product codes, B1:HJ1 contains
week ending dates (ie 10/6/01). B2:HJ4000 contain unit sales (per
product code, per week)

The spreadsheet 'summary' I work within contains (sorry I can't just
paste a pic in):
A1: product code
A3: start date
A4: end date

I am trying to find the sum of a given product code from a start date
to an end date. So within 'summary', cell A6 is
=SUMPRODUCT((ISNUMBER(SEARCH(A1,'the data'!A2:A4000)))*INDEX('the
data'!B2:HJ4000,,MATCH(A3,'the data'!B1:HJ1,0)):INDEX('the
data'!B2:HJ4000,,MATCH(A4,'the data'!B1:HJ1,0)))

This formula works great up to a certain point, but at some point it
starts returning a circular reference error. It seems that it arises
from too many cells needing to be searched. I've tried using smaller
data sets, different dates, etc, and i just can't figure out why it
starts to "break".
Thanks in advance for any advice

 

[pinmaster]

Why not try something more simple like:

=SUMPRODUCT(('the data'!A2:A4000=A1)*('the data'!B1:HJ1>=A3)*('the
data'!B1:HJ1<=A4),B2:HJ4000)

where A3 is the start date and A4 the end date.

HTH
Jean-Guy

 

[hizzle]

Thanks for the reply.
I am worried my formula is a little too complicated, but here is what I
am trying to do:

About A2:A4000, to provide a little more detail, this range actually
contains a combination of a region and product family code, so the
cells would contain: US P214A, US P214, Japan P42, Japan P123, US P214,
Europe P42, etc.

Keys here are:
1. all the parts for each region aren't adjacent (nor are the parts),
2. the text in column A can be of any length
3. multiple cells in column A may contain the same information (like US
P214 above), and I need to sum the units between the dates for both of
those rows. sadly I can't reformat to make things easier...
4. I also need to differentiate between US P214 and US P214A, for
example, but I haven't quite figured out how to do that part in my
formula yet...

 

[pinmaster]

Not sure if this will help but assuming every cell in A2:A4000 has data
and that data consist on a region followed by a code with a space in
between then maybe:

=SUMPRODUCT((RIGHT('the data'!A2:A4000,LEN('the data'!A2:A4000)-FIND("
",'the data'!A2:A4000))=A1)*('the data'!B1:HJ1>=A3)*('the
data'!B1:HJ1<=A4),'the data'!B2:HJ4000)

A1 = code like P42, P123, P124 ...etc
A3 = start date
A4 = end date

but as I said, it will only work if all cells contains 1 space between
the region and code, if only 1 cell doesn't have a space it or is empty
then it will error out!

If that doesn't work then you might think of braking it down by region
and then adding them all together. Put your regions in a column then in
the second column type: =$A$1 copy down then use something like

=SUMPRODUCT(('the data'!$A$2:$A$4000=B1&" "&C1)*(......
where B1 is the region and C1 is the code

HTH
Jean-Guy