sumproduct value error

**dollyj** · 07-28-2011, 05:15 AM

Hi

I need help with the following formula-

SUMPRODUCT((rkc!$B$6:$B$200=Summary!B17)*(rkc!$AB$3:$BB$3=Summary!$W$1),rkc!$AC$6:$BB$200)

B6:B200 is a list of store names in a separate tab to which the formula should lookup B17 which is a particular store

AB3:BB3 is the list of months and W1 is the month to look up

AC6:BB200 are the sales value for each month to sumproduct

I have used the same formula in another worksheet and it has worked but not for this one-I have attached an example-hope you can help

**zbor** · 07-28-2011, 05:25 AM

Re-attach

**dollyj** · 07-28-2011, 05:29 AM

oooh im not sure how i can attach to this site-any idea

**arthurbr** · 07-28-2011, 05:36 AM

Edit original post
Click " Go Advanced"
Clcik the paperclip
Follow the wiz

BTW SUMPRODUCT requires equal length ranges - This is not the case in your formula

**dollyj** · 07-28-2011, 05:55 AM

unfortunately i cannot attach as the website keeps crashing

what do you mean the length isnt the same-i used the same formula in another spreadsheet and it works fine

**zbor** · 07-28-2011, 06:04 AM

Ranges must be same size:

SUMPRODUCT((rkc!$B$6:$B$200=Summary!B17)* (rkc!$BB$3:$BB$197=Summary!$W$1),rkc!$AC$6:$AC$200)

You are limited to 1 MB so keep it smaller, just what's needed for example.