+ Reply to Thread
Results 1 to 15 of 15

Sumif function not calculating correctly

  1. #1
    Registered User
    Join Date
    01-01-2020
    Location
    India
    MS-Off Ver
    office 2016
    Posts
    5

    Unhappy Sumif function not calculating correctly

    I have the problem with sumif function not calculating correctly. I tried all the ways but am unable to solve this issue. In some cell it works and in some cell it doesn't. I have marked it in red color.

    attaching the file below.

    Thanks in advance
    Attached Files Attached Files
    Last edited by shankr; 01-01-2020 at 03:11 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,675

    Re: Sumif function not calculating correctly

    Welcome to the forum.

    You have marked ALL results in red. Please redo the workbook - mark only those that you think are incorrect in red and in the column to the right, type in manually what you believe the answer should be.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    01-01-2020
    Location
    India
    MS-Off Ver
    office 2016
    Posts
    5

    Unhappy Re: Sumif function not calculating correctly

    The Value with correct result is marked Green
    the value with wrong result is marked Red
    and
    the expected result value is marked orange.

    PFA.

    Thanks.
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,675

    Re: Sumif function not calculating correctly

    I don't understand your logic. Explain in WORDS why I9 should be 0.

    Your SUMIFs are working perfectly based on what they are telling Excel to do.
    Last edited by AliGW; 01-01-2020 at 04:03 AM.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,675

    Re: Sumif function not calculating correctly

    From your expected results, I think all you need is this:

    =D8-F8

  6. #6
    Registered User
    Join Date
    01-01-2020
    Location
    India
    MS-Off Ver
    office 2016
    Posts
    5

    Re: Sumif function not calculating correctly

    H8 value is depended on D8 and F8.. .. where H8=D8-F8 marked as Green(calculating correct)
    similarly
    H9 value is depended on D9 and F9..... so H9=D9-F9 =154987 ...marked as Red(calculating wrong)..it should be 0.

    and so on of all further red and green color values.



    Thanks

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,675

    Re: Sumif function not calculating correctly

    As I said, =D8-F8 gives what you say you want:

    Excel 2016 (Windows) 32 bit
    H
    I
    5
    BALANCE
    QTY
    Expected Correct result
    6
    7
    8
    50
    50
    9
    154987
    0
    10
    76952
    67400
    11
    76952
    67400
    12
    90435
    90435
    13
    0
    0
    14
    111608
    0
    15
    168157
    56549
    16
    0
    0
    17
    111608
    56549
    18
    168157
    56549
    19
    0
    0
    20
    0
    0
    21
    154987
    146791
    22
    146791
    146791
    23
    146791
    146791
    24
    146791
    146791
    25
    146791
    146791
    26
    0
    0
    27
    111608
    55060
    28
    168157
    55060
    Sheet: Sheet1

    Excel 2016 (Windows) 32 bit
    I
    8
    =D8-F8
    Sheet: Sheet1

  8. #8
    Registered User
    Join Date
    01-01-2020
    Location
    India
    MS-Off Ver
    office 2016
    Posts
    5

    Re: Sumif function not calculating correctly

    Quote Originally Posted by AliGW View Post
    From your expected results, I think all you need is this:

    =D8-F8
    Ya i can do that ...but i want to know why some are giving correct and some wrong result in same column with same formula?

  9. #9
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    2016
    Posts
    511

    Re: Sumif function not calculating correctly

    SUMIF gives a total of everything in the column that matches your criteria...in that case "PRIMER".

    Untitled.png



    BTW Ali, I've seen you post examples like the above before and is a great way to show things. Could you explain how you link that sample of cells please?
    Last edited by Beamernsw; 01-01-2020 at 04:43 AM.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,675

    Re: Sumif function not calculating correctly

    Quote Originally Posted by shankr View Post
    Ya i can do that ...but i want to know why some are giving correct and some wrong result in same column with same formula?
    SUMIF is the wrong function for what you are doing (see post above this).

    =D8-F8 does not return any errors based on what you said you wanted.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,675

    Re: Sumif function not calculating correctly

    Quote Originally Posted by Beamernsw View Post
    BTW Ali, I've seen you post examples like the above before and is a great way to show things. Could you explain how you link that sample of cells please?
    I use the BB code options in this add-in: https://www.dropbox.com/s/umis204g6e...ools.xlam?dl=0

  12. #12
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    2016
    Posts
    511

    Re: Sumif function not calculating correctly

    Thankyou Ali, much appreciated.

    Yay, my first Excel addin.. LOL

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,675

    Re: Sumif function not calculating correctly

    Enjoy!

    @Shankr

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  14. #14
    Registered User
    Join Date
    01-01-2020
    Location
    India
    MS-Off Ver
    office 2016
    Posts
    5

    Re: Sumif function not calculating correctly

    Thanks for all the responses... The problem got resolved

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,675

    Re: Sumif function not calculating correctly

    Please mark the thread as solved - instructions in post #13.

+ 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. Area Sub Function not calculating correctly
    By cnash52 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-04-2016, 02:58 PM
  2. DAYS function: correctly calculating the number of days
    By dadpad in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-25-2015, 09:18 PM
  3. Sumif function not working correctly
    By chouston in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-14-2014, 04:02 PM
  4. [SOLVED] Formula not calculating correctly
    By ratboyab in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-29-2013, 06:35 PM
  5. [SOLVED] Numbers not calculating correctly
    By Scott727 in forum Excel General
    Replies: 11
    Last Post: 05-24-2012, 01:50 AM
  6. Count function not calculating correctly
    By jetty52 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-27-2011, 08:00 AM
  7. UDF Function to calculate Sheet Offset not calculating correctly
    By maurocam in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-04-2011, 07:57 AM

Tags for this Thread

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