# Sumproduct/Index mismatch?

#### dalian

##### New Member
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

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Jonmo1

##### MrExcel MVP
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...
Code:
``=SUMPRODUCT((INDIRCT("Sales!\$C4:C" & TOPROW)=1)*(INDIRECT("Sales!\$D4:D" & TOPROW)=D10),INDIRECT("Sales!\$L4:L" & TOPROW))``

#### dalian

##### New Member
Works perfectly, thanks!

Replies
2
Views
195
Replies
1
Views
143
Replies
0
Views
119
Replies
3
Views
101
Replies
13
Views
298

1,191,706
Messages
5,988,187
Members
440,136
Latest member
dandanfielding

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back