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

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