sumproduct using indirect

[shodan]

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)

 

[NBVC]

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"

 

[shodan]

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

 

[NBVC]

Why do you need the Indirect function here?

What exactly are you trying to do?

 

[NBVC]

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)

 

[shodan]

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:

INDIRECT(VLOOKUP(Sheet4!D9;VOLUMES!A1:B2;2;FALSE))

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

but,.. it go's wrong somehow.

thanks for your help anyway.

 

[NBVC]

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?

 

[Domenic]

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

Try removing the brackets from your text references...

 

[shodan]

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