+ Reply to Thread
Results 1 to 5 of 5

sumif with multiple cells address

  1. #1
    Registered User
    Join Date
    10-10-2012
    Location
    Dubai
    MS-Off Ver
    Excel 2007
    Posts
    2

    sumif with multiple cells address

    Hi All,
    I have cells that may have #DIV/0!, #VALUE! or #N/A, for example the addresses are:
    A2
    B2
    C3
    D4
    E2

    how to run sumif or sum for these cells, please note that I don't want to run "if" or "iserror" for each cell.

    thanks a lot.
    Sergey
    Last edited by serouja; 10-10-2012 at 06:08 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: sumif with multiple cells address

    Probably better to get rid of the errors in the first place by amending the formulae that produce them. In Excel 2007 you can do this:

    =IFERROR(your_existing_formula,"")

    or you can use 0 instead of the "".

    Hope this helps.

    Pete

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: sumif with multiple cells address

    assuming your range is D5:M80 you can use any of the below array formula.

    =SUMPRODUCT(IFERROR(D5:M80,0))
    or

    =SUM(IFERROR(D5:M80,0))
    Last edited by kvsrinivasamurthy; 10-11-2012 at 02:44 AM.

  4. #4
    Registered User
    Join Date
    10-10-2012
    Location
    Dubai
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: sumif with multiple cells address

    thanks for both replies, but if possible without IFERROR for each cell or IFERROR in the SUM function, thanks again..

  5. #5
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: sumif with multiple cells address

    if cells A1 through A6 have these values:

    1
    2
    #DIV/0!
    #N/A
    #REF!
    5

    you could use the following CSE formula to get the SUM, under the stipulations presented by you so far:

    Please Login or Register  to view this content.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

+ 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