How do I modify my SumProduct formula where #N/A errors are included?

Posted on 2011-10-07
Last Modified: 2012-05-12
Hello again,

My data set actually includes some #N/A errors.  How do I modify my formula to adjust for #N/As present?

Where MySumRange_Dept is:
(The data is text only.)

Thanks, Gary
Question by:garyrobbins
    LVL 41

    Expert Comment

    Try this - it works for me.

    {=SUMPRODUCT(IF(ISERROR(mySumRange),0,mySumRange))} - CTRL-SHIFT-ENTER to confirm

    LVL 50

    Expert Comment

    by:barry houdini
    What are you trying to do exactly? The formula


    ...won't do anything except sum values in the range specified, so SUMPRODUCT is superfluous. I suspect there is more complexity involved - how you handle errors is different depending on what the formula is supposed to do

    If you do just want to sum the values in that range then try using SUMIF


    That will ignore #N/A errors (or any other errors)

    regards, barry
    LVL 50

    Accepted Solution

    OK I didn't see this part

    The data is text only

    If you have a SUMPRODUCT formula of the form


    then that sums col C when A is "x" and B is "y"....but if there might be #N/A errors in A2:A10 only then a way round that is like this


    does that help?


    Author Closing Comment

    Thanks again, Barry.

    I like the ISNumber function...


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now