+ Reply to Thread
Results 1 to 4 of 4

How to deal with error values for the SUMPRODUCT() function?

  1. #1
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    How to deal with error values for the SUMPRODUCT() function?

    Hi, can I ask how to deal with the error values when the SUMPRODUCT() is to be used? I used a formula to sum up these cells as:

    =SUMPRODUCT(--(MOD(COLUMN(A2:H2),2)=0),--(ISNUMBER(A2:H2)),A2:H2)

    If A2:H2 contains error #N/A value, then the formual failed to work.

    Just wondering how to make this work without array?
    Last edited by billj; 01-28-2013 at 06:25 AM.

  2. #2
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Re: How to deal with error values for the SUMPRODUCT() function?

    Still waiting for a solution

  3. #3
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: How to deal with error values for the SUMPRODUCT() function?

    Hi

    How about:
    =SUM(IF(MOD(COLUMN($A$2:$H$2),2)=0,$A$2:$H$2))
    Enter as an array formula. CTRL + SHIFT + ENTER
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  4. #4
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    Re: How to deal with error values for the SUMPRODUCT() function?

    Quote Originally Posted by Kevin UK View Post
    Hi

    How about:
    =SUM(IF(MOD(COLUMN($A$2:$H$2),2)=0,$A$2:$H$2))
    Enter as an array formula. CTRL + SHIFT + ENTER
    Thanks, Kevin. This worked!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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