+ Reply to Thread
Results 1 to 5 of 5

SUMPRODUCT Error

  1. #1
    Registered User
    Join Date
    10-23-2014
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2013
    Posts
    24

    SUMPRODUCT Error

    I have used SUMPRODUCT many times for many things. In a current analysis I am doing, I use it to find assumptions from a tab that has many. The problem I am having is that when I move the range, it is giving me #N/A errors. Here is an example:

    This formula has no errors:
    =SUMPRODUCT((Assumptions!$A$73:$A$79=$A18)*(Assumptions!$B$73:$B$79=$B18)*(Assumptions!$I$10:$CK$10=D$6)*Assumptions!$I$73:$CK$79)

    However, this formula does, even though it is going just one row further down (from 79 to 80):
    =SUMPRODUCT((Assumptions!$A$73:$A$80=$A19)*(Assumptions!$B$73:$B$80=$B19)*(Assumptions!$I$10:$CK$10=D$6)*Assumptions!$I$73:$CK$80)

    Does anyone know why this might be happening?

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,547

    Re: SUMPRODUCT Error

    I would help us to help you if you would attach a sample workbook with the working and non-working formulas applied. Remember to desensitize the data.
    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,942

    Re: SUMPRODUCT Error

    One of the values in row 80 is #N/A. There is nothing wrong with the formula that you are using, so the error must be in the values being evaluated.
    Bernie Deitrick
    Excel MVP 2000-2010

  4. #4
    Registered User
    Join Date
    10-23-2014
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2013
    Posts
    24

    Re: SUMPRODUCT Error

    That was it, Bernie. Thanks for letting me know that. I didn't know that you couldn't have #N/A's in order for this to work. There was a rogue formula, and this solved it. Thanks!

  5. #5
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,942

    Re: SUMPRODUCT Error

    You can have errors in your data - you just need to anticipate that condition.

    You could change

    Assumptions!$I$73:$CK$80

    to

    IFERROR(Assumptions!$I$73:$CK$80,0)

    and enter the formula using Ctrl-Shift-Enter rather than just enter.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sumproduct error
    By dschnetzer in forum Excel General
    Replies: 5
    Last Post: 01-05-2011, 12:36 PM
  2. SUMPRODUCT and #VALUE error
    By kathleen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-18-2010, 03:39 PM
  3. Sumproduct Error
    By ltmaiyk in forum Excel General
    Replies: 4
    Last Post: 11-05-2009, 11:21 AM
  4. Sumproduct error
    By bertman77 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-15-2008, 01:22 PM
  5. Error values:DIV/0! error in SumProduct formula with no division
    By Jerry W. Lewis in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 07:05 PM
  6. [SOLVED] sumproduct error
    By jhahes in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2005, 02:05 PM
  7. [SOLVED] SumProduct Error?
    By john in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-08-2005, 08:06 AM
  8. [SOLVED] SUMPRODUCT ERROR
    By Mestrella31 in forum Excel General
    Replies: 1
    Last Post: 01-26-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1