# sumproduct using indirect

#### shodan

##### Active Member
dear all,

Now I'm trying to use a sumproduct with a indirect formula:

the sumproduct I have is:
=SUMPRODUCT(--(VOLUMES!\$A\$1:\$A\$10000=DATABASE!A4);--(VOLUMES!\$B\$1:\$B\$10000=DATABASE!B4);(VOLUMES!\$C\$1:\$C\$10000))

and I changed it as follows:
=SUMPRODUCT(--(VOLUMES!\$A\$11:\$A\$10010=DATABASE!A4);--(VOLUMES!\$B\$11:\$B\$10010=DATABASE!B4);INDIRECT(VLOOKUP(Sheet4!D9;VOLUMES!A1:B2;2;FALSE)))

If I evaluate the formula, it seems that something is wrong with the indirect resulting in an #REF error.

Edit: where the value in the lookup table is:
(VOLUMES!\$C\$11:\$C\$10010)
Anyone out here wanna help me. (I know, I have been asking lots of questions today, sorry)

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Try placing the Sheet4!D9 within the Vlookup in Double quotations as:

"Sheet4!D9"

...or around the Sheet4!D9;VOLUMES!A1:B2 portion as "Sheet4!D9;VOLUMES!A1:B2"

Tried both things, but still get an error???

Why do you need the Indirect function here?

What exactly are you trying to do?

I got this to give me a numerical result.

Does this do in place of your Indirect function?

VLOOKUP(Sheet4!D9,INDIRECT("VOLUMNS!A"&1&":B"&2),2,FALSE)

I have a multiconditional sumif, using the sumproduct formula. now what I am trying is to make the range which needs to be summed, a variable one.

quote:
--------------------------------------------------------------------------------
=SUMPRODUCT(--(VOLUMES!\$A\$1:\$A\$10000=DATABASE!A4);--(VOLUMES!\$B\$1:\$B\$10000=DATABASE!B4);(VOLUMES!\$C\$1:\$C\$10000))
--------------------------------------------------------------------------------

So the last part (VOLUMES!\$C\$1:\$C\$10000) must become variable. therefore I tried to use the indirect vlookup, because I know my ranges in front. As you can see in the example, I replaced this last part by:
Code:
``INDIRECT(VLOOKUP(Sheet4!D9;VOLUMES!A1:B2;2;FALSE))``

where this vlookup value = (VOLUMES!\$C\$1:\$C\$10000)

but,.. it go's wrong somehow.

What do you mean by variable...the ranges of the table change in size or the location of the table changes?

How do think that the Indirect function will help? What's in Vlookup!A1:B2 and in Sheet4!D9?

shodan said:
...where this vlookup value = (VOLUMES!\$C\$1:\$C\$10000)...

Try removing the brackets from your text references...

Domenic, you are good !! that did it again. why can i not figure this out myself?!

NBVC,thanks for the efforts anyway , I appreciate it.

Regards

Replies
1
Views
189
Replies
2
Views
485
Replies
7
Views
155
Replies
1
Views
298
Replies
3
Views
441

1,196,042
Messages
6,013,047
Members
441,746
Latest member
ArtemisAlex

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