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

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

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

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...

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