Sum by order number 1

[colkas]

Hi

If I have a spreadsheet with order number and totals for year. However we have 2 types of order direct and stock. This is show by the order number, direct as a - sign in. We would like a total for both types. Example below

Order NO Article Amount Toal Sum Direct TotalSum Stock
1234 300 20 40 40
1234-5 300 30
1235 300 20
345-6 300 10


These lists are very large so we need to be able do a sum on order numbers like *-* and then not like *-*

How do we do a formula fo thses calcualtions.

Thanks in advance

 

[DaleWatson123321]

These formulas should fit...

For Total Sum Direct: =SUMIF(A:A,"*-*",C:C)

For Total Sum Stock: =SUM(C:C) minus the cell containing the above formula

Regards, Dale Watson

 

[colkas]

Ok one thing I forgot sorry,

we need the amounts per article number

Order NO Article Amount Toal Sum Direct TotalSum Stock
1234 300 20 40 40
1234-5 300 30
1235 300 20
345-6 300 10

23-45 100 20
2345 200 10


so we need the totals by article number for direct and stock, how do we do the calcualtion by article, as one article could be on many different order lines. So in this example the result would be something like below


Article Total direct Total stock
300 40 40
200 10
100 20

The of course we can use the minus forumula. Any ideas

Thanks

 

[KenWright]

Various ways to do it, but a pivot Table would do this in a minute, and I mean literally 60 seconds. See here for an intro, and it'll be the best bit of Excel education you'll get for free:-

http://peltiertech.com/Excel/Pivots/pivotstart.htm

If you really want a formula then you firstly need a unique set of Article numbers. If it's only a couple then do it manually, or if it's lots then use Data / Filter / Advanced Filter / 'Copy to another location' and 'Unique values' only to get that.

Once you have it then you can use the SUMPRODUCT function to give you counts or sums based on more than one criteria, and the following link is a great intro to that:-

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

It would work as follows, assuming your example data in say A1:E1000

J K
1 Direct Stock
2 100
3 200
4 300
5 400

Cell J2 for Direct is

=SUMPRODUCT(($B$2:$B$27=$J2)*(ISNUMBER(FIND("-",$A$2:$A$27)))*($C$2:$C$27))

and just copy down.

Cell J2 for Stock is then

=SUMPRODUCT(($B$2:$B$1000=$J2)*(ISERROR(FIND("-",$A$2:$A$1000)))*($C$2:$C$1000))

and just copy down.


For the stock you could also just use

=SUMPRODUCT(($B$2:$B$1000=$J2)*($C$2:$C$1000)) and then minus the Direct number

Another option might be to use the DSUM functions as well

Regards
Ken....................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[colkas]

Hi

Sorry lot of information there and I cannot get any of it to work. This is the simple layout, however, in reality this list could be hundreds long.

Article Order NoAmount
100 123-1 500.00
100 1234 300
200 124-1 400.00
200 124-2 600.00

So I need to toal by articl no, then total for the article number for direct and stock with articl number against them.
Article Total Direct stock
100 800 500 300
200 1000 10000 1000

Any chance of simplifying your answer. Many thanks, i will keep trying in the mean time.

 

[KenWright]

You probably can't get it to work because I based the ranges on your original post and you have now reversed the data that was in Cols A & B.

What are the exact ranges of your data, and be very specific about what is in what column. Give us the range that your source data is in, ie the data you are summarising is

Article Order No Amount
100 123-1 500.00
100 1234 300
200 124-1 400.00
200 124-2 600.00

Tell us that this is in say A1:C567, and that row has the headers.

Then tell us exactly where you are putting the summary table, eg

Article Total Direct stock
100 800 500 300
200 1000 10000 1000

J1:M10 or whatever.

The formulas I gave you started at row 2 assuming that row contains your headers - they cannot be in the Sum range.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[colkas]

Hi

I will try, this is how my simplified sheet looks


A B C
1 Article Order Amount
2 100 123-1 500
3 100 1234 300
4 200 124-1 400
5 200 124-2 600

I can get a pivot so it shows article and the totals, but then i canot get a total on direct orders (ones with a "-") in.

We would like the result in the same spreadsheet in new columns, again this is a simple veersion, our correct spreadsheet is alot more populated, if I can get the basic working I can apply to the working one.

So the result columns would be

Article Total Direct stock
100 800 500 300
200 1000 10000 1000

The direct is the ones with order numbers with "-" in andstock would be total - direct

Hope you can help, thanks

 

[KenWright]

If you have the Pivot table working then I would stick with that route, but in order to make it easier to wdo i would suggest the addition of 1 more column if that is OK. This would be a flag that denotes Direct or Stock and would be a simple thing such as in an adjoining column say Col D, and assuming your order numbers are in Col B starting in B2, then in D2 put

=IF(ISERROR(FIND("-",B2)),"Stock","Direct")

and copy down.

Label it Direct/Stock, include that field in your Pivot Source range and simply use that in the COLUMN fields when you build your table.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[colkas]

HI

Good the new column helped alot there, seems logical when you know how.

Okay I now have a pivot table with direct and stock totals. Finally, I think, how do I then update another sheet with the totals by article only for stock quatitys.

Tahnks for your help.

 

[KenWright]

LOL - You already have it. From the Pivot Table list, just drag the Article field into the ROW fields, just the way you put the Direct/Stock field into The COLUMN fields. You can now either link to this data, or even just copy the pivot table into the sheet where youw ant it and it will be live there as well.

Once you have all that working, if you think you will be adding more data at all, then you may want to consider a dynamic named range as the source for your pivot table, such that as you add items to the source table, the increased range is automatically used by the Pivot table. You would need to at least refresh the Pivot table once but that's a simple click. For info on how to do that if you wanted, take a look here:-

http://www.contextures.com/xlPivot01.html

Depending on what other data you have in your source sheet, you can use the Pivot table to group data by dates if you have them. You can group into months/years etc in no time at all.

Regards
Ken...............



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[colkas]

Hi

Not sure I understand as usally, This is how my pivot table looks and is in sheet 4. All the main data is in sheet one.

So how do I connect or link it, dragging hte fileds does not work. Thanks

Article Stock/Direct amount
100 800
Direct 500
Stock 300

200 1000
Direct 600
Stock 400

Grand Total 1800

 

[KenWright]

When you created the Pivot table you should have dragged the Direct/Stock field to the COLUMN fields, the Article field to the ROW fields, and the Amount field to the DATA area. This will give you table that looks like this

Sum of Amount  Direct/Stock		
Article	  ¦ Direct	Stock	¦ Grand Total
------------------------------------------
100	      ¦ 1300	   2500    ¦   3800
200	      ¦ 2800	    400    ¦   3200
300	      ¦	        2300	¦   2300
----------------------------------------
Grand Total  ¦ 4100       5200	¦   9300

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------