Sumproduct with changing columns

Question

Hi,

I have a Sumproduct functions where I multiply 2 columns.

the problem is that the columns may change.

I can find the 2 columns to multiply very easy (MATCH function). But how do I incorporate this information in the SUMPRODUCT function?

I would like something like:

=SUMPRODUCT(Column(4),Column(8))

where 4 and 8 are parameters.

Thanks,

Yuval

Answer 1

Hi, I found a solution that works. I used the Address function with Indirect function.

=SUMPRODUCT(INDIRECT(B496),INDIRECT(B497))

where in B496 I have:

=ADDRESS(5,Summaries!B494,,,"Macro Status")&":"&ADDRESS(74,Summaries!B494)

(I am referring to rows 5 to 74)

and in B497 I have:

=ADDRESS(5,Summaries!B495,,,"Macro Status")&":"&ADDRESS(74,Summaries!B495)

B494 and B495 hold the column numbers that I want to use.

regards,

Yuval

Answer 2

Hi,

Maybe this.

In G1 e.g. A1:A3, in H1 B1:B3

=SUMPRODUCT=SUMPRODUCT(INDIRECT(G1),INDIRECT(H1))

By changing G1 & H1, you can multiply other ranges.

Answer 3

hi Yuval.  if your SUMPRODUCT formula looks like this:

=SUMPRODUCT(C1:C10,0,E1:E10)

and the heading you want to match is "a" & "c" text, then

=SUMPRODUCT(INDEX(A1:F10,0,MATCH("a",1:1,0)),INDEX(A1:F10,0,MATCH("c",1:1,0)))