Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Posts
    5

    Unanswered: Formula to sum numbers above a specific value

    Hi Everyone,

    I'm trying to keep track of sales to specific customers, and I wonder if you can help me. I have the sales in one row, with the customer code in the row below it. I've provided a small illustration below:

    Black 1000 250 300
    Customer AA AA BB

    White 150
    Customer CC

    Navy 500
    Customer DD

    Yellow 625
    Customer BB

    Red 900
    Customer CC


    Is there any way to have the total sales to customers AA, BB, CC and DD at the bottom? I suppose I would need to get the sum of all cells ABOVE the required customer.

    Thanks for your help.

  2. #2
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Welcome to the Board!

    Unfortunately, your existing table design doesn't lend itself to ready analysis.

    See if the attached example helps out any.

    Smitty

    EDIT: Typo
    Attached Files Attached Files

  3. #3
    Join Date
    Apr 2004
    Posts
    5
    Hi Smitty,

    I've looked at your table. The main difference is that I need to sum up from different, non-consecutive rows. I have included an excel sheet that I have converted to text which I hope will better illustrate what I'm trying to do.

    Thanks.
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2004
    Posts
    5
    Does anyone else have any idea how to sum non-consecutive rows, as per the attachment above?

    Any help would be much appreciated.

    Thanks.

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    After looking at your example, I wonder if the entire setup needs work. It seems confusing at best. Perhaps I don't understand what you are trying to do (certainly not the first time that has happened!!).

    Just for general guidelines... (you may already know this, but maybe others can benefit)

    Normally for database ability (which seems to be what you want), an extra row for customer number is awkward and unnecessary; rather an extra column for customer code on the same line seems better suited.

    Unless setting up this spreadsheet for display and data entry (which is not a good idea), the extra rows/columns between data makes Excel nothing more than a high-priced calculator. In other words all the tools (sum, sumif, sumproduct, subtotals, pivot tables, etc.) that make Excel work so well are thwarted by the design.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Apr 2004
    Posts
    5
    In this case, the reason that I thought to include the customer name/ID below the order, is because there will be many orders throughout the period. Therefore, I need the room to go as far right as possible. Even if I did place the customer info in the column beside it, there would be multiple entries. An example is attached.

    You see...there will be multiple orders of each color, but I do not know how many.

    Any ideas?
    Attached Files Attached Files

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, try this. Use three columns on sheet1:

    Column A: Customer (put this in A1)

    Column B: Color (put this in B1)

    Column C: Quantity (put this in C1)

    Now, you would have one order for each customer order. To help, use Validation for column B. If you want to insure specific colors, then make a list of colors on another worksheet, and name it something like MyColors and have it refer to all the colors: (i.e., sheet2:B3:B9)

    Then on sheet1, in cell B2 select it, and go to Data > Validation, and choose List. In the formula box put this: =MyColors Then click OK.

    Now copy cell B2 down as far as you want. Now, when you add an order, you select from the dropdown list in column B.

    Does this help?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    You would be far better off keeping individual orders on their own rows & data columns. It's pretty hard to maintain a true database format otherwise. You'll also be able to use Data Validation Lists to quickly select color & customer choices

    With your data set up that way, this would be a perfect job for a Pivot Table, which will make all of your calculations for you.

    If you think that you'll exceed Excel's row capacity (65,536), then you could consider moving this to Access.

    Smitty

    Here's an example:
    Attached Files Attached Files

  9. #9
    Join Date
    Oct 2003
    Posts
    1,091
    Great minds think alike, Smitty!

    (or more properly, one great mind [yours], and one worn-out mind [mine])
    Last edited by shades; 04-06-04 at 13:14.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  10. #10
    Join Date
    Apr 2004
    Posts
    5
    Is there any way to do that pivot table, but with each new order entered in a new column? I've attached a document that shows 2 possible ways to structure this sheet. I would prefer the second. Can I do a pivot table if the data is structured that way?
    Attached Files Attached Files

  11. #11
    Join Date
    Oct 2003
    Posts
    1,091
    Neither is set up to make use of Pivot tables. What you seem to want to do is create the Pivot Table as the order sheet. Not a good idea.

    By using the setup as I explained or as Smitty put in his attachment, you can use 65535 rows for orders, and Pivot Table works great. And if necessary, you could then do the next 65,535 rows of orders in columns E-G (skip column D as a separator), or even better add another sheet with the same format - you can add as many sheets as memory will alow.

    Also, in addition to the Pivot Table use, you could now use a Summary sheet and use the SUM and SUMIF functions across sheets for even more info.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •