Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sum by selecting each product
Hi all comunity.
I have this probelm: I have an inventory in Excel with many (maybe 1000) products, like this table show, but some product are in different location , what I want is to sum total of qty by product in another sheet putting a link with a formula, but his need to sum for example: Tomatoes should be a total of: 10 Apple should be a total of: 25 summing Location A2 and A5 Pear should be a total of: 38 summing Location A1,A6 and A9 Location Product Qty A1 Tomatoes 10 A1 Pear 18 A2 Apple 10 A4 Banana 15 A5 Apple 15 A6 Pear 10 A9 Pear 10 anybody can help me, it will be great.. Thank you ldiaz |
#2
|
|||
|
|||
ldiaz,
Select your table, and then use Data / Pivot Table and drop the product name into the row, and the count into the data area. Change the field setting to SUM and you're done. HTH, Bernie MS Excel MVP "ldiaz" wrote in message ... Hi all comunity. I have this probelm: I have an inventory in Excel with many (maybe 1000) products, like this table show, but some product are in different location , what I want is to sum total of qty by product in another sheet putting a link with a formula, but his need to sum for example: Tomatoes should be a total of: 10 Apple should be a total of: 25 summing Location A2 and A5 Pear should be a total of: 38 summing Location A1,A6 and A9 Location Product Qty A1 Tomatoes 10 A1 Pear 18 A2 Apple 10 A4 Banana 15 A5 Apple 15 A6 Pear 10 A9 Pear 10 anybody can help me, it will be great.. Thank you ldiaz |
#3
|
|||
|
|||
....when using the pivot table, as Bernie suggested, you would double click
quantity to change the field settings. You could also put location in the column area. This would, for example, tell you how many pears you have in a given location and the total number of pears. I would look like this... A1 A6 A9 Total Pear 18 10 10 38 "ldiaz" wrote: Hi all comunity. I have this probelm: I have an inventory in Excel with many (maybe 1000) products, like this table show, but some product are in different location , what I want is to sum total of qty by product in another sheet putting a link with a formula, but his need to sum for example: Tomatoes should be a total of: 10 Apple should be a total of: 25 summing Location A2 and A5 Pear should be a total of: 38 summing Location A1,A6 and A9 Location Product Qty A1 Tomatoes 10 A1 Pear 18 A2 Apple 10 A4 Banana 15 A5 Apple 15 A6 Pear 10 A9 Pear 10 anybody can help me, it will be great.. Thank you ldiaz |
#4
|
|||
|
|||
I forgot to tell you that I can't do that because I'm taking those datas from an Intranet excel file and I have only "Read Only" access because that file is used by Warehouse purposes. I know how work a Pivot Table, but I want not that, I like to do something like I asked, could you please help me again, sorry do not put this first. ldiaz "JR" wrote: ...when using the pivot table, as Bernie suggested, you would double click quantity to change the field settings. You could also put location in the column area. This would, for example, tell you how many pears you have in a given location and the total number of pears. I would look like this... A1 A6 A9 Total Pear 18 10 10 38 "ldiaz" wrote: Hi all comunity. I have this probelm: I have an inventory in Excel with many (maybe 1000) products, like this table show, but some product are in different location , what I want is to sum total of qty by product in another sheet putting a link with a formula, but his need to sum for example: Tomatoes should be a total of: 10 Apple should be a total of: 25 summing Location A2 and A5 Pear should be a total of: 38 summing Location A1,A6 and A9 Location Product Qty A1 Tomatoes 10 A1 Pear 18 A2 Apple 10 A4 Banana 15 A5 Apple 15 A6 Pear 10 A9 Pear 10 anybody can help me, it will be great.. Thank you ldiaz |
#5
|
|||
|
|||
1diaz,
Use Data / Import External Data, select the file, and then base your pivot table on that data. HTH, Bernie MS Excel MVP "ldiaz" wrote in message ... I forgot to tell you that I can't do that because I'm taking those datas from an Intranet excel file and I have only "Read Only" access because that file is used by Warehouse purposes. I know how work a Pivot Table, but I want not that, I like to do something like I asked, could you please help me again, sorry do not put this first. ldiaz "JR" wrote: ...when using the pivot table, as Bernie suggested, you would double click quantity to change the field settings. You could also put location in the column area. This would, for example, tell you how many pears you have in a given location and the total number of pears. I would look like this... A1 A6 A9 Total Pear 18 10 10 38 "ldiaz" wrote: Hi all comunity. I have this probelm: I have an inventory in Excel with many (maybe 1000) products, like this table show, but some product are in different location , what I want is to sum total of qty by product in another sheet putting a link with a formula, but his need to sum for example: Tomatoes should be a total of: 10 Apple should be a total of: 25 summing Location A2 and A5 Pear should be a total of: 38 summing Location A1,A6 and A9 Location Product Qty A1 Tomatoes 10 A1 Pear 18 A2 Apple 10 A4 Banana 15 A5 Apple 15 A6 Pear 10 A9 Pear 10 anybody can help me, it will be great.. Thank you ldiaz |
#6
|
|||
|
|||
I appreciate your help...
But as these datas have many modification in a day, it can not be possible,, is there any way to do that I want... I appreciate your help....and I'm sure you can help me.. Ldiaz "Bernie Deitrick" wrote: 1diaz, Use Data / Import External Data, select the file, and then base your pivot table on that data. HTH, Bernie MS Excel MVP "ldiaz" wrote in message ... I forgot to tell you that I can't do that because I'm taking those datas from an Intranet excel file and I have only "Read Only" access because that file is used by Warehouse purposes. I know how work a Pivot Table, but I want not that, I like to do something like I asked, could you please help me again, sorry do not put this first. ldiaz "JR" wrote: ...when using the pivot table, as Bernie suggested, you would double click quantity to change the field settings. You could also put location in the column area. This would, for example, tell you how many pears you have in a given location and the total number of pears. I would look like this... A1 A6 A9 Total Pear 18 10 10 38 "ldiaz" wrote: Hi all comunity. I have this probelm: I have an inventory in Excel with many (maybe 1000) products, like this table show, but some product are in different location , what I want is to sum total of qty by product in another sheet putting a link with a formula, but his need to sum for example: Tomatoes should be a total of: 10 Apple should be a total of: 25 summing Location A2 and A5 Pear should be a total of: 38 summing Location A1,A6 and A9 Location Product Qty A1 Tomatoes 10 A1 Pear 18 A2 Apple 10 A4 Banana 15 A5 Apple 15 A6 Pear 10 A9 Pear 10 anybody can help me, it will be great.. Thank you ldiaz |
#7
|
|||
|
|||
If the data changes, simply refresh the data extract and the pivot table. A
formula-based solution would be problematic due the the changing nature of the extract anyway, so use the capabilities of Excel to your advantage. HTH, Bernie MS Excel MVP "ldiaz" wrote in message ... I appreciate your help... But as these datas have many modification in a day, it can not be possible,, is there any way to do that I want... I appreciate your help....and I'm sure you can help me.. Ldiaz "Bernie Deitrick" wrote: 1diaz, Use Data / Import External Data, select the file, and then base your pivot table on that data. HTH, Bernie MS Excel MVP "ldiaz" wrote in message ... I forgot to tell you that I can't do that because I'm taking those datas from an Intranet excel file and I have only "Read Only" access because that file is used by Warehouse purposes. I know how work a Pivot Table, but I want not that, I like to do something like I asked, could you please help me again, sorry do not put this first. ldiaz "JR" wrote: ...when using the pivot table, as Bernie suggested, you would double click quantity to change the field settings. You could also put location in the column area. This would, for example, tell you how many pears you have in a given location and the total number of pears. I would look like this... A1 A6 A9 Total Pear 18 10 10 38 "ldiaz" wrote: Hi all comunity. I have this probelm: I have an inventory in Excel with many (maybe 1000) products, like this table show, but some product are in different location , what I want is to sum total of qty by product in another sheet putting a link with a formula, but his need to sum for example: Tomatoes should be a total of: 10 Apple should be a total of: 25 summing Location A2 and A5 Pear should be a total of: 38 summing Location A1,A6 and A9 Location Product Qty A1 Tomatoes 10 A1 Pear 18 A2 Apple 10 A4 Banana 15 A5 Apple 15 A6 Pear 10 A9 Pear 10 anybody can help me, it will be great.. Thank you ldiaz |
#8
|
|||
|
|||
I will try like you say,
anyway thank you so much for your help and best regards from Tijuana Mexico.. Ldiaz "Bernie Deitrick" wrote: If the data changes, simply refresh the data extract and the pivot table. A formula-based solution would be problematic due the the changing nature of the extract anyway, so use the capabilities of Excel to your advantage. HTH, Bernie MS Excel MVP "ldiaz" wrote in message ... I appreciate your help... But as these datas have many modification in a day, it can not be possible,, is there any way to do that I want... I appreciate your help....and I'm sure you can help me.. Ldiaz "Bernie Deitrick" wrote: 1diaz, Use Data / Import External Data, select the file, and then base your pivot table on that data. HTH, Bernie MS Excel MVP "ldiaz" wrote in message ... I forgot to tell you that I can't do that because I'm taking those datas from an Intranet excel file and I have only "Read Only" access because that file is used by Warehouse purposes. I know how work a Pivot Table, but I want not that, I like to do something like I asked, could you please help me again, sorry do not put this first. ldiaz "JR" wrote: ...when using the pivot table, as Bernie suggested, you would double click quantity to change the field settings. You could also put location in the column area. This would, for example, tell you how many pears you have in a given location and the total number of pears. I would look like this... A1 A6 A9 Total Pear 18 10 10 38 "ldiaz" wrote: Hi all comunity. I have this probelm: I have an inventory in Excel with many (maybe 1000) products, like this table show, but some product are in different location , what I want is to sum total of qty by product in another sheet putting a link with a formula, but his need to sum for example: Tomatoes should be a total of: 10 Apple should be a total of: 25 summing Location A2 and A5 Pear should be a total of: 38 summing Location A1,A6 and A9 Location Product Qty A1 Tomatoes 10 A1 Pear 18 A2 Apple 10 A4 Banana 15 A5 Apple 15 A6 Pear 10 A9 Pear 10 anybody can help me, it will be great.. Thank you ldiaz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Rows/Columns for Copying Formulas | Excel Discussion (Misc queries) | |||
Percentages | Charts and Charting in Excel | |||
How to set a formula to count the product appear how manytime | Excel Worksheet Functions | |||
Which function(s)? | Excel Worksheet Functions | |||
If statement needed | Excel Worksheet Functions |