• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct query

Phil

New Member
I am having problems with my Sumproduct formula.


If I type the following, it returns a value correctly.

=SUMPRODUCT(($L$5:$DS$5=D$1434)*($I$7:$I$50=$B1441),$L$7:$DS$50)


If I increase the data range, it returns a #N/A error.

=SUMPRODUCT(($L$5:$DS$5=D$1434)*($I$7:$I$500=$B1441),$L$7:$DS$500)


The data range is made up entirely of values.


Thanks
 
Not sure if this will help, but when I have had similar problems I have added on a +0) to the end of the formula and then it works


=SUMPRODUCT((($L$5:$DS$5=D$1434)*($I$7:$I$500=$B1441),$L$7:$DS$500)+0)
 
Hi Phil,


Welcome to the Forums!!


The formula is giving your #N/A Error because you are only increasing the last argument to $L$7 while the criteria range is still L5:DS5, try using this formula:


Code:
=SUMPRODUCT(($L$7:$DS$5=D$1434)*($I$7:$I$500=$B1441),$L$7:$DS$500)


Regards,
 
Hi Phil ,


I tried your formula , in both its versions , and neither of them gave me a #N/A error.


Can you post the following cell contents ?


1. Which of the cells L5:DS5 contains a match for the value in D1434


2. Which of the cells I7:I500 contains a match for the value in B1441


Narayan
 
Thankyou for your quick responses.


I just couldn't see how there could be an error in the formula so I checked for errors in the data table. I found an error in row 4000 (about that row). Once I fixed the error my formula worked. I should have checked that earlier and not wasted your time!


Thankyou again. I also have to say i have found the forum overall to be very informative.


Phil
 
Back
Top