+ Reply to Thread
Results 1 to 10 of 10

Sumifs returns 0

  1. #1
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    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
    2013
    Posts
    1,669

    Re: Sumifs returns 0

    Use this formula instead:

    HTML Code: 
    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 Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    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
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    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
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Sumifs returns 0

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

  7. #7
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Sumifs returns 0

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

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    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 Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    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)

Similar Threads

  1. [SOLVED] sumifs returns 0
    By intothewild in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-21-2016, 09:18 AM
  2. USING SUMIFS formula returns a '0' for me
    By jlp83 in forum Excel General
    Replies: 7
    Last Post: 03-12-2015, 06:53 AM
  3. [SOLVED] sumifs formula returns error
    By kronikjb in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-08-2015, 12:51 PM
  4. [SOLVED] Sumifs returns zeroes
    By Bob Macregor in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-01-2014, 07:17 AM
  5. [SOLVED] SUMIFS returns a zero value
    By jtc121 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-07-2014, 06:29 AM
  6. Sumifs formula returns ZERO
    By Xx7 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-10-2013, 02:05 PM
  7. 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