Sumproduct multiplying instead of adding

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

denise - 15 Jun 2007 20:29 GMT

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

Reply to this Message

bj - 15 Jun 2007 20:44 GMT

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

> Hi folks,
>
[quoted text clipped - 11 lines]
> Thanks,
> Denise

Reply to this Message

Rick Rothstein (MVP - VB) - 15 Jun 2007 20:50 GMT

> 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
[quoted text clipped - 9 lines]
> 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

Reply to this Message

denise - 15 Jun 2007 21:11 GMT

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

Many thanks,
Denise

> > 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
[quoted text clipped - 16 lines]
>
> Rick

Reply to this Message

T. Valko - 15 Jun 2007 21:26 GMT

You don't need the SUMPRODUCT function.

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

Will do.

Biff

> Thanks bj and Rick! Yes, the plus sign takes care of the problem. I'm glad
> it
[quoted text clipped - 29 lines]
>>
>> Rick

Reply to this Message