+ Reply to Thread
Results 1 to 16 of 16

To sum or not to sum

  1. #1
    Registered User
    Join Date
    03-26-2013
    Location
    Copenhagen , Denark
    MS-Off Ver
    Excel 2007
    Posts
    7

    To sum or not to sum

    eksperiment.xlsx

    Hey guys!

    So... Excel has been kicking my *** for the last couple of hours, and I was hoping that someone might be able to help the silly me

    A Picture to illustrate:

    Excel kicking my ***.jpg

    What I want to achive is for the Price to be summed under total price, but only when the ordernumber is the same. So in this case I want the sum of 75+100+150 in F19 because all the items are from the same order.

    First i tried with the IF function, but didn't get there, and now with the SUMIF (SUM.HVIS in Danish, and ";" instead of ",").

    They idea here was to make is sum when the ordernumber was the same (A16=A17), but it doesn't Work.

    Anybody WHO can help?

    Oh and it would be really cool if it only summed in the last of the rows of the same ordernumber (row 19 for order 1)


    Anybody?

    Thanks a bunch!


    Martin
    Last edited by FMSlater; 03-26-2013 at 06:24 PM. Reason: Attaching file...

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,108

    Re: To sum or not to sum

    Hi and welcome to the forum

    Next time, please upload your sample workbook, not a picture of it - no1 wants to re-type your data for you.

    Try using sumif() or sumifS()
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: To sum or not to sum

    how about a pivot table....check this out....
    Attached Files Attached Files
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  4. #4
    Registered User
    Join Date
    03-26-2013
    Location
    Copenhagen , Denark
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: To sum or not to sum

    Quote Originally Posted by FDibbins View Post
    Hi and welcome to the forum

    Next time, please upload your sample workbook, not a picture of it - no1 wants to re-type your data for you.

    Try using sumif() or sumifS()
    Thanks

    Alright, attached my file.

    It's just a file to experiment in

  5. #5
    Registered User
    Join Date
    03-26-2013
    Location
    Copenhagen , Denark
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: To sum or not to sum

    Quote Originally Posted by judgeh59 View Post
    how about a pivot table....check this out....
    That's brilliant!

    However, in this case I need a permanent list with all the values are displayed in an row after row kind of order, and since there are a lot of columns and rows I would like it if I only had to enter a few numbers, and it would simply do the rest itself. Thats why I would like it to a formula

  6. #6
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: To sum or not to sum

    I guess I would have to see more data than in the JPG and the attachment, didn't attach....

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,108

    Re: To sum or not to sum

    actually the attachment, did attach. However, you will need to include some sample answers as well plz

  8. #8
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: To sum or not to sum

    @FDibbins - I'm not seeing the attachment...what am I doing wrong?

  9. #9
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: To sum or not to sum

    I found it.....I didn't expect to find it in the OP....

  10. #10
    Registered User
    Join Date
    03-26-2013
    Location
    Copenhagen , Denark
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: To sum or not to sum

    New file.xlsx

    Thanks for your replies so far! I've attached a more detailed file.

    So what I'm trying to do is to get it to calculate the red numbers by itself with a formula?

    Is is doable?

  11. #11
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: To sum or not to sum

    i see what you are trying to do, but I'm still going to recomment a PT....my reasoning is that looking at your data, are the order numbers always in order?...a PT would care....you can add data and then just refresh and the new data (Totals) are updated....what you want can be done...but this is what PT were made for...
    Attached Files Attached Files

  12. #12
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: To sum or not to sum

    that is a PT WOULDN'T care.....my fingers are dumb sometimes....

  13. #13
    Registered User
    Join Date
    03-26-2013
    Location
    Copenhagen , Denark
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: To sum or not to sum

    Quote Originally Posted by judgeh59 View Post
    i see what you are trying to do, but I'm still going to recomment a PT....my reasoning is that looking at your data, are the order numbers always in order?...a PT would care....you can add data and then just refresh and the new data (Totals) are updated....what you want can be done...but this is what PT were made for...
    Oh, but I want to succeed. You say it can be done. Any pointers? With a SUMIF or IF/AND/OR combo?

  14. #14
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: To sum or not to sum

    in the sample workbook that you uploaded, put this in cell F16, then drag-fill down:

    Please Login or Register  to view this content.
    does that help?

    UPDATE:

    in case you need to increase the number of rows in this formula, please update to the highest row number in the highlighted portion only.

    this formula assumes that your order_number column will be sorted, i.e., all "like" order_number records will be adjacent to each other. please let me know if that is an issue and i can try to see if there is another way to approach this problem.
    Last edited by icestationzbra; 03-26-2013 at 09:19 PM.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  15. #15
    Registered User
    Join Date
    03-26-2013
    Location
    Copenhagen , Denark
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: To sum or not to sum

    Quote Originally Posted by icestationzbra View Post
    in the sample workbook that you uploaded, put this in cell F16, then drag-fill down:

    Please Login or Register  to view this content.
    does that help?

    You're kiddig me...

    It absolutely WORKS!

    That's just awefully AWESOME!

    ...and I'm afraid there's NO way I've would have ever been able to think that up! Now I see how long from home I actually was.

    Well thanks a bunch! That couldn't have helped more


    The best to you all!


    Martin

  16. #16
    Registered User
    Join Date
    03-26-2013
    Location
    Copenhagen , Denark
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: To sum or not to sum

    Quote Originally Posted by FMSlater View Post
    You're kiddig me...

    It absolutely WORKS!

    That's just awefully AWESOME!

    ...and I'm afraid there's NO way I've would have ever been able to think that up! Now I see how long from home I actually was.

    Well thanks a bunch! That couldn't have helped more


    The best to you all!


    Martin
    Okay, it Works. However it turn out that I'm unable to adjust it so that it allows me to always add more rows (as new orders are made). I simply tried increasing the countting range of the formula, but then it got destroyed

    Could I possibly get you to ajust it so that i can keep adding new rows of data to it? It seems to be bound to only a certain area, and my limited understanding takes me nowhere fast!

    And is there a limit in this formula to how many items that can be in one order?

    If anyone can help I'd appreciate it

+ 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