Sumproduct/Index mismatch?

[dalian]

Hello everybody,

I have a worksheet called "Sales" which is built as a small database with a fixed number of columns (22) and around 200 rows for the moment (will increase in the future). This range is called "ALLDATA". The data itself starts at row 3; row 2 (TOPROW) only contains parameters that refer to the letter of the column and to the number of lines in ALLDATA, so for example I will have the following :

Row 2, column A -> ="$A"&ROWS(ALLDATA),
Row 2, column B -> ="$B"&ROWS(ALLDATA)
Etc.

What I am trying to do is retrieve data from column L of this sheet and sum it in sheet "Analysis" based on two conditions : product name (col D of "Sales") and type (col C) and I am using "sumproduct" for that, but for some reason, the following isn't working -

=SUMPRODUCT(((Sales!$C4:INDEX(TOPROW,0,3))="1")*((Sales!$D4:INDEX(TOPROW,0,4))=D10),((Sales!$L4:INDEX(TOPROW,0,12))))

Since the number of rows in "Sales" varies on a regular basis, I don't want to hard-code anything in the formula, but it seems that the problem comes from combining "sumproduct" and "index" in the same formula, and my question is how could I make this work?

Any help will be appreciated,

Thanks,

Dalian

 

[Jonmo1]

I think the problem is that your named Range TOPROW is a 1 Cell Reference. A200 or something. And your Index formula is trying to retrn the the 3rd column of that. There's no such column...

My best advice would be to begin by hard coding the Ranges into the formula until it works.

THEN work on naming the ranges.
You also have way too many parentheses...
maybe you could use indirect here...

=SUMPRODUCT((INDIRCT("Sales!$C4:C" & TOPROW)=1)*(INDIRECT("Sales!$D4:D" & TOPROW)=D10),INDIRECT("Sales!$L4:L" & TOPROW))

 

[dalian]

Works perfectly, thanks!