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

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

....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

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

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

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

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

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