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