Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table - untick all items in row field | Excel Discussion (Misc queries) | |||
How to group multiple items in to three groups. | Excel Worksheet Functions | |||
Pivot Table: How can I organize data items in a row? | Excel Discussion (Misc queries) | |||
multiple items in database | Excel Worksheet Functions | |||
Can't group pivot table items by month in Excel | Excel Discussion (Misc queries) |