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.

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

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.

Does anyone else have any idea how to sum non-consecutive rows, as per the attachment above?

Any help would be much appreciated.

Thanks.

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.

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?

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?

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:

Great minds think alike, Smitty!

(or more properly, one great mind [yours], and one worn-out mind [mine])

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?

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.