+ Reply to Thread
Results 1 to 6 of 6

Creating sort proof formulas

  1. #1
    Registered User
    Join Date
    12-06-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Creating sort proof formulas

    I'm sure there is a solution to the following problem however I can't seem to find it. Hopefully someone will be kind enough to guide me in the proper direction.


    Basicly I have a sheet which is setup something like below and I want to sum the volumes from company 1 and company 2 aswell as total volumes of each product which is fine until I have a need to resort the sheet after I resort the sheet the totals are adjusted to reflect the new value of the cell range I have in my formula. How do I create a formula that will maintain the correct result regardless of how I sort the data?



    DATE Company Product Volume
    oct 11 company1 product1 6
    oct 12 company2 product1 4
    nov 21 company1 product1 5

  2. #2
    Registered User
    Join Date
    12-06-2011
    Location
    Durham, NC
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Creating sort proof formulas

    Try using the SUMIFS function instead of just a SUM. SUMIFS allows you to sum based on a criteria, for example, you can create a formula to sum volume only where company=company1. The way the data is sorted will not affect this formula. The function arguments popup makes it pretty self-explainatory, but post again if you need further help and I can walk you through it.

  3. #3
    Registered User
    Join Date
    12-06-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Creating sort proof formulas

    I tried using the following formula with no luck.

    in column F i have the amount of product and in column D I have the product names.
    so I am trying to sum everything in column F if column D is "product1"

    =SUMIFS(F1:F87,D1:D87,"product1")

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Creating sort proof formulas

    Try
    Please Login or Register  to view this content.
    Alf

  5. #5
    Registered User
    Join Date
    12-06-2011
    Location
    Durham, NC
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Creating sort proof formulas

    Might be a compatability issue, try the SUMIF function, in your case it would be =SUMIF(D1:D87,"product1",F1:F87).

    Let me know if that one works.

  6. #6
    Registered User
    Join Date
    12-06-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Creating sort proof formulas

    Thanks now how would I go about checking to see if 2 columns are correct before adding a third?

+ 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