Sum like items
 

I have a report that keeps track of:
Product #, Customer #, Qty Sold for every invoice on a specific date

I want to add/SUM all the (Qty Sold) for all Like (Product #'s) regardless
who they were purchased by. To give me my total units sold.

And also be able to SUM the (Qty Sold) for each specific Customer. To give
me that Customers Total Useage.

Sum like items
 

Hi

One way, put Product item in F2 and Customer name in G2
=SUMPRODUCT(--($A$2:$A$1000=F2),$C$2:$C$1000) for total Product units
sold
=SUMPRODUCT(--($B$2:$B$1000=G2),$C$2:$C$1000) for Total sold to a given
Customer
=SUMPRODUCT(--($A$2:$A$1000=F2),--($B$2:$B$1000=G2),$C$2:$C$1000) for
Given Customer and Given product


Better still, create a Pivot Table and see an analysis of all Customers
by all Products.
For help on setting up a Pivot Table take a look at Debra Dalgleish's
site and scroll down to Pivot Tables
http://www.contextures.com/tiptech.html
--
Regards

Roger Govier


"JCowell" wrote in message
...
I have a report that keeps track of:
Product #, Customer #, Qty Sold for every invoice on a specific date

I want to add/SUM all the (Qty Sold) for all Like (Product #'s)
regardless
who they were purchased by. To give me my total units sold.

And also be able to SUM the (Qty Sold) for each specific Customer. To
give
me that Customers Total Useage.

Sum like items
 

I am trying to do this for over 200,000 unique items and hundreds of customers.

I can't put item in F2 for thousands of items. Can I?

"Roger Govier" wrote:

Hi

One way, put Product item in F2 and Customer name in G2
=SUMPRODUCT(--($A$2:$A$1000=F2),$C$2:$C$1000) for total Product units
sold
=SUMPRODUCT(--($B$2:$B$1000=G2),$C$2:$C$1000) for Total sold to a given
Customer
=SUMPRODUCT(--($A$2:$A$1000=F2),--($B$2:$B$1000=G2),$C$2:$C$1000) for
Given Customer and Given product


Better still, create a Pivot Table and see an analysis of all Customers
by all Products.
For help on setting up a Pivot Table take a look at Debra Dalgleish's
site and scroll down to Pivot Tables
http://www.contextures.com/tiptech.html
--
Regards

Roger Govier


"JCowell" wrote in message
...
I have a report that keeps track of:
Product #, Customer #, Qty Sold for every invoice on a specific date

I want to add/SUM all the (Qty Sold) for all Like (Product #'s)
regardless
who they were purchased by. To give me my total units sold.

And also be able to SUM the (Qty Sold) for each specific Customer. To
give
me that Customers Total Useage.

Sum like items
 

I did say a Pivot Table would be a far better idea.

However, I you truly do have over 200,000 Unique Items, then Excel is
not the answer for you (currently), as there is a limit of 65536 rows
per sheet. Assuming that more than one customer bought at least one of
these items, then the size of the database will be huge and needs to be
in a database application, not in a spreadsheet.

--
Regards

Roger Govier


"JCowell" wrote in message
...
I am trying to do this for over 200,000 unique items and hundreds of
customers.

I can't put item in F2 for thousands of items. Can I?

"Roger Govier" wrote:

Hi

One way, put Product item in F2 and Customer name in G2
=SUMPRODUCT(--($A$2:$A$1000=F2),$C$2:$C$1000) for total Product units
sold
=SUMPRODUCT(--($B$2:$B$1000=G2),$C$2:$C$1000) for Total sold to a
given
Customer
=SUMPRODUCT(--($A$2:$A$1000=F2),--($B$2:$B$1000=G2),$C$2:$C$1000) for
Given Customer and Given product


Better still, create a Pivot Table and see an analysis of all
Customers
by all Products.
For help on setting up a Pivot Table take a look at Debra Dalgleish's
site and scroll down to Pivot Tables
http://www.contextures.com/tiptech.html
--
Regards

Roger Govier


"JCowell" wrote in message
...
I have a report that keeps track of:
Product #, Customer #, Qty Sold for every invoice on a specific
date

I want to add/SUM all the (Qty Sold) for all Like (Product #'s)
regardless
who they were purchased by. To give me my total units sold.

And also be able to SUM the (Qty Sold) for each specific Customer.
To
give
me that Customers Total Useage.

Sum like items
 

I do appreciate your help. I will look into and try to figure out pivot
tables.

I'm a salesman for a fishing tackle company and we have always told our
customer that we have over 200,000 independent SKU's between three
warehouses. However, odly enough when I look at the total number of lines on
our catalog that I just recently downloaded I only show 61,483.

I'm an honest salesman if there ever was one and I'll definately quiet
promoting our over 200,000 SKU's.


Thanks again-

Justin Cowell

"Roger Govier" wrote:

I did say a Pivot Table would be a far better idea.

However, I you truly do have over 200,000 Unique Items, then Excel is
not the answer for you (currently), as there is a limit of 65536 rows
per sheet. Assuming that more than one customer bought at least one of
these items, then the size of the database will be huge and needs to be
in a database application, not in a spreadsheet.

--
Regards

Roger Govier


"JCowell" wrote in message
...
I am trying to do this for over 200,000 unique items and hundreds of
customers.

I can't put item in F2 for thousands of items. Can I?

"Roger Govier" wrote:

Hi

One way, put Product item in F2 and Customer name in G2
=SUMPRODUCT(--($A$2:$A$1000=F2),$C$2:$C$1000) for total Product units
sold
=SUMPRODUCT(--($B$2:$B$1000=G2),$C$2:$C$1000) for Total sold to a
given
Customer
=SUMPRODUCT(--($A$2:$A$1000=F2),--($B$2:$B$1000=G2),$C$2:$C$1000) for
Given Customer and Given product


Better still, create a Pivot Table and see an analysis of all
Customers
by all Products.
For help on setting up a Pivot Table take a look at Debra Dalgleish's
site and scroll down to Pivot Tables
http://www.contextures.com/tiptech.html
--
Regards

Roger Govier


"JCowell" wrote in message
...
I have a report that keeps track of:
Product #, Customer #, Qty Sold for every invoice on a specific
date

I want to add/SUM all the (Qty Sold) for all Like (Product #'s)
regardless
who they were purchased by. To give me my total units sold.

And also be able to SUM the (Qty Sold) for each specific Customer.
To
give
me that Customers Total Useage.

Sum like items
 

Hi Justin

Well aren't all salesmen honest!! <bg

I guess that in any time span you are looking at, not every one of the
SKU's will have moved and the problem will be smaller than you first
imagine.
Do take the time to learn and play with Pivot Tables.
They are fascinating, and your efforts will be well rewarded.
I do believe there's an excellent book on its way from Debra in the next
month - unless the publishers miss their deadlines of course!!

--
Regards

Roger Govier


"JCowell" wrote in message
...
I do appreciate your help. I will look into and try to figure out
pivot
tables.

I'm a salesman for a fishing tackle company and we have always told
our
customer that we have over 200,000 independent SKU's between three
warehouses. However, odly enough when I look at the total number of
lines on
our catalog that I just recently downloaded I only show 61,483.

I'm an honest salesman if there ever was one and I'll definately quiet
promoting our over 200,000 SKU's.


Thanks again-

Justin Cowell

"Roger Govier" wrote:

I did say a Pivot Table would be a far better idea.

However, I you truly do have over 200,000 Unique Items, then Excel is
not the answer for you (currently), as there is a limit of 65536 rows
per sheet. Assuming that more than one customer bought at least one
of
these items, then the size of the database will be huge and needs to
be
in a database application, not in a spreadsheet.

--
Regards

Roger Govier


"JCowell" wrote in message
...
I am trying to do this for over 200,000 unique items and hundreds of
customers.

I can't put item in F2 for thousands of items. Can I?

"Roger Govier" wrote:

Hi

One way, put Product item in F2 and Customer name in G2
=SUMPRODUCT(--($A$2:$A$1000=F2),$C$2:$C$1000) for total Product
units
sold
=SUMPRODUCT(--($B$2:$B$1000=G2),$C$2:$C$1000) for Total sold to a
given
Customer
=SUMPRODUCT(--($A$2:$A$1000=F2),--($B$2:$B$1000=G2),$C$2:$C$1000)
for
Given Customer and Given product


Better still, create a Pivot Table and see an analysis of all
Customers
by all Products.
For help on setting up a Pivot Table take a look at Debra
Dalgleish's
site and scroll down to Pivot Tables
http://www.contextures.com/tiptech.html
--
Regards

Roger Govier


"JCowell" wrote in message
...
I have a report that keeps track of:
Product #, Customer #, Qty Sold for every invoice on a specific
date

I want to add/SUM all the (Qty Sold) for all Like (Product #'s)
regardless
who they were purchased by. To give me my total units sold.

And also be able to SUM the (Qty Sold) for each specific
Customer.
To
give
me that Customers Total Useage.

Sum like items
 

Why dont u use simple Pivot Table Wizard ? Why to go for macro When
simple solution is available!


--
ExcelPower
------------------------------------------------------------------------
ExcelPower's Profile: http://www.excelforum.com/member.php...o&userid=30964
View this thread: http://www.excelforum.com/showthread...hreadid=507793