+ Reply to Thread
Results 1 to 8 of 8

Sumifs returns zeroes

  1. #1
    Registered User
    Join Date
    09-25-2014
    Location
    PEI, Canada
    MS-Off Ver
    Excel 2007
    Posts
    7

    Sumifs returns zeroes

    I'm using the SUMIFS function to assemble unique values into a summary table in a different layout than the source table. The source is four columns with 171 rows and the destination is 10 columns with 60 rows. Setting conditions on three of the source columns (A, B and D) determines a unique value for the fourth (the value being exported), but when that condition doesn't exist, I get a zero in the target cell. Since I will be graphing the results, I want to be able to differentiate between a true zero (of which there are only a couple), and a zero that is just a place-holder in the table; I would like my graph lines to have gaps where there are no values. Note that the target cells are not entered as array formulas, but I get correct values, except for the filler zeroes.
    I tried a nested IF(AND() with the three conditions in the AND and an array as the iftrue result, but got all FALSE values. I also tried error trapping with an if false=>"" type of command before my sum, but failed there, too.

    I'm attaching a version of the database that shows the problem.

    AYSample.xlsx

    Bob

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Sumifs returns zeroes

    Hi

    maybe a possible approach
    Please Login or Register  to view this content.
    Cheers
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Sumifs returns zeroes

    Try In h2 apply the below, it will return "" if not available

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Boo?ath?

  4. #4
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Sumifs returns zeroes

    bit late

  5. #5
    Registered User
    Join Date
    09-25-2014
    Location
    PEI, Canada
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Sumifs returns zeroes

    BINGO! A pretty simple fix (that I just couldn't see for myself).
    Thanks to both canapone and boopathiraja for your help.

    Bob

  6. #6
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Sumifs returns zeroes

    Hi,

    thanks for providing us feedback.

    Cheers

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Sumifs returns zeroes

    You could also use your original formula with IFERROR used for zero suppression.

    =IFERROR(1/(1/(SUMIFS($C$2:$C$172,$A$2:$A$172,$F2,$B$2:$B$172,$G2,$D$2:$D$172,RIGHT(H$1,4)))),"")
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  8. #8
    Registered User
    Join Date
    09-25-2014
    Location
    PEI, Canada
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Sumifs returns zeroes

    Thanks, AlKey. I'll keep this in mind for next time I face a similar issue.

+ 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. [SOLVED] SUMIFS returns a zero value
    By jtc121 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-07-2014, 06:29 AM
  2. Sumifs formula returns ZERO
    By Xx7 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-10-2013, 02:05 PM
  3. sumif returning correct total while sumifs returns #value
    By mlevitt in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-01-2013, 04:16 PM
  4. [SOLVED] SUMIFS only returns the 1st matched value in my range of criterias
    By catzilla in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-30-2012, 11:55 AM
  5. SUMIFS returns VALUE!
    By mogdog1981 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-03-2011, 04:38 AM

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