#REF! Error in INDEX & MATCH Formula!

Hi Friends,

In the attached file, From A10:A20, I have a list of different items. From B10:B20 each item group is been mentioned and from C10:P20 day wise inventory qty is been mentioned.

Above said details, I have to do week wise summary in B2:H5 and I have written a formula which is giving either wrong result (as shown in C3) or #REF! Error!

Need your help to solve this issue…

Thanks & Regards,
Rajeshkumar R

the index has to be a range.
try in c3

=SUMPRODUCT(--(OFFSET($B$11,0,MATCH(C$2,$C$10:$P$10,0),10)>=0),--($B$11:$B$20=$B3),OFFSET($B$11,0,MATCH(C$2,$C$10:$P$10,0),10))

this will give totals for each on specified date >=0

in your summary table, use this formula, copied across and down...

=SUMPRODUCT(($C$11:$P$20)*($C$11:$P$20>0)*($B$11:$B$20=$B3)*($C$10:$P$10>=C$2)*($C$10:$P$10<D$2))

@ Martin, i was just comparing your formula to mine and curious about the different approaches. you use offset, and --, could you explain to me why you took this approach please? (always looking to learn new ways)

its just the first thing that came to mind! plus we get entirely different results!
i'm just calculating the column that matches the dates in c2:h2

i checked your results, and if you look at the 1st set of values for X May 21-May 27, the total is 450 (100+150+200). your total comes up to 100?

oh wait, i see where the difference comes in. your formula mathes the dates, mine adds the dates for the week from may21 to may 72