+ Reply to Thread
Results 1 to 9 of 9

Sumifs giving strange results but when saved the results seemd to be normal partly

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Sumifs giving strange results but when saved the results seemd to be normal partly

    Hi All

    I have a workings sheet and summary sheet .

    Using sumifs formula on summary tariff column c and d but in entering formula and dragging gives same numbers on both columns but in saving the value changes in column C and D but still the total at end of columns does not tally with workings sheets total.

    I have tried on excel 2007 , excel 2013 and excel 2016 having the same issues .

    example using trim on column A and B in summary tariff gave the same issue column F and G and H gives the same results but saving the results became good!!!

    I d' ont where the problem is coming.

    Can anybody help to sort out
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Sumifs giving strange results but when saved the results seemd to be normal partly

    You have calculations set to manual on the Formulas Tab.
    Change that to Automatic.

  3. #3
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Sumifs giving strange results but when saved the results seemd to be normal partly

    It's also worth noting that Flour, Oat Flakes, and Honey all get listed twice (with the same HS Code) on the Summary Report. Those duplicates account for the difference (629) between your totals in C195 of your Summary Report and G765 of your Workings sheet.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Sumifs giving strange results but when saved the results seemd to be normal partly

    Is there supposed to be a one to one match between a code and its description? That would seem logical

    Note however that there are two descriptions for code 19054090, similarly code 19049000, and for 19059090 there are 4 descriptions plus many other anomalies.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: Sumifs giving strange results but when saved the results seemd to be normal partly

    Well I used the same data with pivot table on excel 2016 I got the right answer with main database that is amount of pivot table tally with database product.

    But however when I used advanced filter with unique values then copy to summary sheet , I got the same answer it did not tally for the hs code and products description

    I am trying to figure out why this happens with advanced filter unique ??? where as pivot table 2016 it works . but the issue I am working with excel 2007 at work.this is causing me a lot of trouble and making mad .

    This is the first time I got this issue as it is a new supplier ,I got the data excel sheet from them.

    See attached done with pivot table 2016 giving correct results in yellow font and red at far right of the sheet.
    Attached Files Attached Files

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Sumifs giving strange results but when saved the results seemd to be normal partly

    Quote Originally Posted by JEAN1972 View Post
    Well I used the same data with pivot table on excel 2016 I got the right answer with main database that is amount of pivot table tally with database product.
    But what's the answer to the question about the one to one mach between code and description.
    Your Pivot Table will of course report the correct overall total since all codes are covered. But because many codes have two or more descriptions you will get strange answers if you try to compare totals for a code and totals for what you believe is the code description.

    For instance. Look at your pivot table in A29 & A30. The two codes are the same but the description is different. ConfectionARY and ConfectionERY
    Similarly with A44 & A45 and many others.

    You really do need to tidy up your data so that it is consistent otherwise you'll continue to have anomalies.

  7. #7
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: Sumifs giving strange results but when saved the results seemd to be normal partly

    ok thanks for review I just trim clean the whole database

    Now It fine , the issue was the supplier database was completely a mess.

    Now its ok

    Thanks for help

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Sumifs giving strange results but when saved the results seemd to be normal partly

    Hi,

    You need to do more than TRIM stuff. There are spelling differences to correct as well.

  9. #9
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: Sumifs giving strange results but when saved the results seemd to be normal partly

    Yes i have noticed and thanks for help first time get such messy file i have informed the guy and it states that its from an export interface

+ 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. vlookup giving #N/A results
    By Jazzzbo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-18-2014, 12:28 AM
  2. VLOOKUP not giving apt results....
    By lifeisaspreadsheet in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-04-2012, 04:36 AM
  3. Strange RAND results
    By tangent1 in forum Excel General
    Replies: 3
    Last Post: 05-08-2010, 11:23 AM
  4. Replies: 3
    Last Post: 03-31-2010, 11:47 AM
  5. Macro worked for 2 years, suddenly started giving strange results
    By dylanemcgregor in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-21-2009, 04:39 AM
  6. [SOLVED] Strange results...
    By Jim May in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-24-2006, 05:30 PM
  7. Strange IF results
    By MWE in forum Excel General
    Replies: 0
    Last Post: 03-25-2005, 08:14 PM

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