Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database summary
I have a workbook with 2 spreadsheets. In the first one, I have a column of
products that has a corresponding customer and the customer may appear more than once. I want to query that sheet and in the second sheet, have the customer list with the sum of quantity of products that customer purchased. All products are equivalent in size just different genetics. I just want to know how many each customer purchased. Sheet 1 customer1 product1 qty customer2 product1 qty customer2 product2 qty customer2 product3 qty customer3 product1 qty customer3 product2 qty customer4 product1 qty customer4 product2 qty Sheet 2 customer1 sum customer2 sum customer3 sum cusomter4 sum |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database summary
"Todd Vohs" wrote: I have a workbook with 2 spreadsheets. In the first one, I have a column of products that has a corresponding customer and the customer may appear more than once. I want to query that sheet and in the second sheet, have the customer list with the sum of quantity of products that customer purchased. All products are equivalent in size just different genetics. I just want to know how many each customer purchased. Sheet 1 customer1 product1 qty customer2 product1 qty customer2 product2 qty customer2 product3 qty customer3 product1 qty customer3 product2 qty customer4 product1 qty customer4 product2 qty Sheet 2 customer1 sum customer2 sum customer3 sum cusomter4 sum Hi. I think that you could use the "SUMIF" function. If,say, the customer names are in column A, rows 1 to 10, and their purchases are in column B, rows 1 to 10, then you could run a search thus : Label A11 to A20 Customer 1, Customer2, & etc.. In cell B11, code : =sumif(A1:A10,"Customer 1",B1:B10) In cell B11 you will then have the sum (taken from column B (rows 1 to 10)) of any name and column A (rows 1 to 10) which matches the target name (in this first case, Customer 1) In cell B12, code: =sumif(A1:A10,"Customer 2",B1:B10) In cell B13, code: =sumif(A1:A10,"Customer 3",B1:B10) I hope this helps |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database summary
On Nov 1, 6:28 pm, Pete wrote:
"Todd Vohs" wrote: I have a workbook with 2 spreadsheets. In the first one, I have a column of products that has a corresponding customer and the customer may appear more than once. I want to query that sheet and in the second sheet, have the customer list with the sum of quantity of products that customer purchased. All products are equivalent in size just different genetics. I just want to know how many each customer purchased. Sheet 1 customer1 product1 qty customer2 product1 qty customer2 product2 qty customer2 product3 qty customer3 product1 qty customer3 product2 qty customer4 product1 qty customer4 product2 qty Sheet 2 customer1 sum customer2 sum customer3 sum cusomter4 sum Hi. I think that you could use the "SUMIF" function. If,say, the customer names are in column A, rows 1 to 10, and their purchases are in column B, rows 1 to 10, then you could run a search thus : Label A11 to A20 Customer 1, Customer2, & etc.. In cell B11, code : =sumif(A1:A10,"Customer 1",B1:B10) In cell B11 you will then have the sum (taken from column B (rows 1 to 10)) of any name and column A (rows 1 to 10) which matches the target name (in this first case, Customer 1) In cell B12, code: =sumif(A1:A10,"Customer 2",B1:B10) In cell B13, code: =sumif(A1:A10,"Customer 3",B1:B10) I hope this helps- Hide quoted text - - Show quoted text - Todd - see my email - Pete is right if you want a sum of the amount each customer has spent, countif would be used to count the number each customer purchased Polly |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database summary
=SUMIF(Sheet1!A:A,A1,Sheet1!C:C)
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Todd Vohs" wrote in message ... I have a workbook with 2 spreadsheets. In the first one, I have a column of products that has a corresponding customer and the customer may appear more than once. I want to query that sheet and in the second sheet, have the customer list with the sum of quantity of products that customer purchased. All products are equivalent in size just different genetics. I just want to know how many each customer purchased. Sheet 1 customer1 product1 qty customer2 product1 qty customer2 product2 qty customer2 product3 qty customer3 product1 qty customer3 product2 qty customer4 product1 qty customer4 product2 qty Sheet 2 customer1 sum customer2 sum customer3 sum cusomter4 sum |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database summary
Hi Todd -
If you don't need a customized format for the summed list, a Pivot Table might do the job. Make sure you first add field names to the top of your data columns, e.g., Customer, ProductName, Quantity. Then: 1. Choose Data, Pivot Table... and press [Next] (to accept the default data source in Step 1 of the Pivot Table Wizard). 2. Highlight your data list (including the field names) and press [Next]. 3. Press [Finish] in Step 3 of the Wizard. This will give you a blank Pivot Table and a PivotTable field list. 4. Drag the 'Customer' fieldname from the field list to the "Drop Row Fields Here" section of the blank Pivot Table and drag the 'Quantity' fieldname to the "Drop Data Items Here" section. --- Jay "Todd Vohs" wrote: I have a workbook with 2 spreadsheets. In the first one, I have a column of products that has a corresponding customer and the customer may appear more than once. I want to query that sheet and in the second sheet, have the customer list with the sum of quantity of products that customer purchased. All products are equivalent in size just different genetics. I just want to know how many each customer purchased. Sheet 1 customer1 product1 qty customer2 product1 qty customer2 product2 qty customer2 product3 qty customer3 product1 qty customer3 product2 qty customer4 product1 qty customer4 product2 qty Sheet 2 customer1 sum customer2 sum customer3 sum cusomter4 sum |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Excel Database via Access Main Database with a script | New Users to Excel | |||
database query not showing foxpro database How I import data | New Users to Excel | |||
Using Excel as a database and need macro or vba to take data entered on one tab and update the database by adding to the next avail row | Excel Programming | |||
multi group with summary above with 1 overall summary line below | Excel Discussion (Misc queries) | |||
multi group with summary above with 1 overall summary line below | Excel Discussion (Misc queries) |