Sumproduct multiplying instead of adding

[denise]

Hi folks,

I'm hoping an expert can help me here. I have a sumproduct formula that
isn't working the way I expected it would. The first part of the function
correctly returns '8' and the second part correctly returns '27' but I'm
looking for these two results to sum to 41 and instead I get 216. I'm sure
it's probably the way I've written this formula. Any hints on how to fix this
would be greatly appreciated.

=SUMPRODUCT(INDEX(stytd!$A$1:$AP$400,MATCH("adoracion
lum",stytd!$A$1:$A$400,0),MATCH("my world international city
rates",stytd!$A$1:$AP$1,0)),(INDEX(qtr1!$A$1:$AP$400,MATCH("lum",qtr1!$A$1:$A$400,0),MATCH("mwi city rates",qtr1!$A$1:$AP$1,0))))

Thanks,
Denise

 

[bj]

change the
....stytd!$A$1:$AP$1,0)),(INDEX...
to
....stytd!$A$1:$AP$1,0))+(INDEX...

 

[Rick Rothstein \(MVP - VB\)]

I'm hoping an expert can help me here. I have a sumproduct formula that

isn't working the way I expected it would. The first part of the function
correctly returns '8' and the second part correctly returns '27' but I'm
looking for these two results to sum to 41 and instead I get 216. I'm sure
it's probably the way I've written this formula. Any hints on how to fix
this
would be greatly appreciated.

=SUMPRODUCT(INDEX(stytd!$A$1:$AP$400,MATCH("adoracion
lum",stytd!$A$1:$A$400,0),MATCH("my world international city
rates",stytd!$A$1:$AP$1,0)),(INDEX(qtr1!$A$1:$AP$400,MATCH("lum",qtr1!$A$1:$A$400,0),MATCH("mwi
city >
rates",qtr1!$A$1:$AP$1,0))))

Does changing the comma separating the two parts to a plus sign do what you
want? If so, isn't all you are doing, then, is summing the first range and
adding it to the sum of the second range? Or am I misreading what your
formula is doing?

Rick

 

[denise]

Thanks bj and Rick! Yes, the plus sign takes care of the problem. I'm glad it
was simple.

Many thanks,
Denise

 

[T. Valko]

You don't need the SUMPRODUCT function.

=INDEX(.......)+INDEX(.......)

Will do.

Biff