# Thread: Formula to sum numbers above a specific value

1. Registered User
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.

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

See if the attached example helps out any.

Smitty

EDIT: Typo

3. Registered User
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.

4. Registered User
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. Registered User
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.

6. Registered User
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?

7. Registered User
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?

8. Registered User
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:

9. Registered User
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.

10. Registered User
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?

11. Registered User
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.

#### Posting Permissions

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