+ Reply to Thread
Results 1 to 10 of 10

Sumifs returns 0

  1. #1
    Registered User
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office7
    Posts
    31

    Sumifs returns 0

    Hi All,
    I using sumif formula in the attached excel and getting result 0 (cell A13)

    when I do the same with sumif for each condition seperately it returns the correct number.

    Can you please help to figure out what is wrong with the formula?

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2007
    Posts
    1,414

    Re: Sumifs returns 0

    Use this formula instead:

    HTML Code: 
    =SUMIF(A2:A11,A2,B2:B11)+SUMIF(A2:A11,A6,B2:B11)
    The way you wrote it, both conditions have to be met.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Sumifs returns 0

    You could do something like this, but it would require your conditions to be in a contiguous range:
    Please Login or Register  to view this content.
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  4. #4
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    4,803

    Re: Sumifs returns 0

    Alternatively
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    or
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Valued Forum Contributor Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    1,123

    Re: Sumifs returns 0

    The criteria in a SUMIFS function work as AND criteria all criteria have to be met to sum the records that comply

    if you want to select 2 values from the samelist then you need to use 2 sumifs (or maybe an array formula)

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office7
    Posts
    31

    Re: Sumifs returns 0

    Thanks. I dont think I understand why my formula doesnt work if both conditions are indeed met.

  7. #7
    Registered User
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office7
    Posts
    31

    Re: Sumifs returns 0

    OH I see now see all the comments.
    Thansk for all your suggestions and explanations !

  8. #8
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    4,803

    Re: Sumifs returns 0

    For your formula to work col A has to equal both 320.revenues row & 330.revenues - intercompany for the same row.
    i.e. A2 would have to equal both values.

  9. #9
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Sumifs returns 0

    You're looking for either condition to be met (OR), not both (AND). What SUMIFS does is require all conditions to be met (AND) at the same time, meaning cells A2:A11 would need to equal both A2 and A6. That's why your formula was returning 0, because both conditions couldn't be met. Hope that explains it!

  10. #10
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    4,803

    Re: Sumifs returns 0

    Quote Originally Posted by belinda200 View Post
    Thansk for all your suggestions and explanations !
    You're welcome & thanks for the feedback

+ 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