Sumproduct formula "breaks" when referencing too m

[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 (thanks to biff! on this board! ), but at some point when I use wider date ranges it starts returning a circular reference error. I'm guessing 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

 

[barry houdini]

Re: Sumproduct formula "breaks" when referencing t

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 (thanks to biff! on this board! ), but at some point when I use wider date ranges it starts returning a circular reference error. I'm guessing 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

Perhaps it would work better if you used a helper column, e.g in 'the data'!HK2 copied down to HK4000

=SUMIF(B$1:HJ$1,">="&summary!A$3,B2:HJ2)-SUMIF(B$1:HJ$1,">"&summary!A$4,B2:HJ2)

then change your SUMPRODUCT formula to

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,'the data'!A2:A4000)),'the data'!HK2:HK4000)

btw does 'the data'!A2:A4000 contain other data as well as the product code? If not then you don't need to use the ISNUMBER(SEARCH.... type construction...

 

[Aladin Akyurek]

All that does not sound convincing. BTW, if you need to multiply a vector with a (possibly multicolumn) matrix, it's better to switch to a Sum/If idiom. Also, as Barry notes, are you sure you need the IsNumber/Search bit?

 

[hizzle]

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

Yikes!

 

[Aladin Akyurek]

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

Yikes!

Would you post an example condition value that you might have in A1?

BTW: Are you on Excel 2003?

 

[hizzle]

Hi-
I am hopping between Excel 2003 and also Excel for the Mac.

Regarding A1 in the "summary" tab, an example would be that I type in:
US P214

and then cell A6 in the summary tab (the main formula I am trying to figure out), would return the sum of all units between the start and end dates input for ANY row in the "data" tab that has
US P214
in column A. so that could possibly be a handful of non-adjacent rows. And it would need to ignore any rows with
US P214A
in column A

I currently am using "helper" columns in the "data" tab that do simple vlookups to calculate the sums for certain products between the dates specified. Then I sum the entire column (about 4000 cells) and then use the result. The problem is I need to be able to enter a whole bunch of different products and different start/end dates on the summary tab. Because of that, it would be *much* easier to have a one-cell formula instead of the use of helper rows, I believe. I have a workaround with these helper rows that functions, but the file winds up being about 100MB (really only because of all the helper columns). Ugh!

 

[Aladin Akyurek]

Hi-
I am hopping between Excel 2003 and also Excel for the Mac.

Regarding A1 in the "summary" tab, an example would be that I type in:
US P214

and then cell A6 in the summary tab (the main formula I am trying to figure out), would return the sum of all units between the start and end dates input for ANY row in the "data" tab that has
US P214
in column A. so that could possibly be a handful of non-adjacent rows. And it would need to ignore any rows with
US P214A
in column A

...

Does 'the data'!A2 or any other cell house always one entry like:

US P214

or multiple entries, separated from each other with a delimiter, like:

US P214, US P214A, Japan P123

with comma as delimiter?

 

[hizzle]

Hello-

The cells in 'the data'A2 (all the way down to 'the data'A4000), always holds just one region/product combination. There are about 10 different possible regions and hundreds of different products.

Sorry - I should have been more clear...
Thanks-

 

[Aladin Akyurek]

Hello-

The cells in 'the data'A2 (all the way down to 'the data'A4000), always holds just one region/product combination. There are about 10 different possible regions and hundreds of different products.

Sorry - I should have been more clear...
Thanks-

A6:

=SUM(IF('the data'!$A$2:$A$4000=A1,INDEX('the data'!$B$2:$HJ$4000,0,MATCH(A3,'the data'!$B$1:HJ1,0)):INDEX('the data'!$B$2:$HJ$4000,0,MATCH(A4,'the data'!B1:HJ1,0))))

which needs to be confirmed with control+shift+enter, not just with enter.

Is A1:HJ4000 fixed or is it dynamic, i.e., expanding/crimping?