| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup and sum of multiple lines
I am importing productivity data from our pos system into worksheet A and
trying to use it to sum up some totals per employee and put it in worksheet B to calculate commission. I would like to do a lookup for the employee and then add up his totals for(in this example) air filters and Breathers. So the lookup should return 25.98 for DUMA,ABE or 21.98 for DAVIS,AL. Is this possible with lookups and sums? column A column B Employee: DUMA,ABE Average Time Per Car Item ================== TOTAL INVOICES 7 CUSTOMER VISITS: ****************** LUBE ONLY: ****************** AIR FILTERS: 14.99 BREATHER FILTERS: 10.99 Employee: DAVIS,AL Average Time Per Car Item ================== TOTAL INVOICES 12 CUSTOMER VISITS: ****************** LUBE ONLY: ****************** AIR FILTERS: 7.99 BREATHER FILTERS: 13.99 -- Thanks Roch |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup and sum of multiple lines
When you want to Analize data in Excel you need to arrange it in a Database
or Table format first, with headings like EmplName Date TotalInvoices CustmVisits ItemSold Amt Then each row is a transaction record. Once the data is arranged like this you can easily use DataFilterAutofilter to get information about any employee/date/Sales etc and apply commissions to the data or subtotals. The Subtotal function, in fact, has several forms that perform many types of anal on filtered data of this kind. "Rock" wrote: I am importing productivity data from our pos system into worksheet A and trying to use it to sum up some totals per employee and put it in worksheet B to calculate commission. I would like to do a lookup for the employee and then add up his totals for(in this example) air filters and Breathers. So the lookup should return 25.98 for DUMA,ABE or 21.98 for DAVIS,AL. Is this possible with lookups and sums? column A column B Employee: DUMA,ABE Average Time Per Car Item ================== TOTAL INVOICES 7 CUSTOMER VISITS: ****************** LUBE ONLY: ****************** AIR FILTERS: 14.99 BREATHER FILTERS: 10.99 Employee: DAVIS,AL Average Time Per Car Item ================== TOTAL INVOICES 12 CUSTOMER VISITS: ****************** LUBE ONLY: ****************** AIR FILTERS: 7.99 BREATHER FILTERS: 13.99 -- Thanks Roch |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup and sum of multiple lines
If I arrange my data as you say
A B Paul 114 george 80 Paul 20 How would I get all the Paul to add up with a VLOOKUP, something like this =VLOOKUP(O12, B1:G3000,SUBTOTAL(9,G1:G3000),FALSE) I know this one doesn't work but am i going in the right direction Thanks "gocush" wrote: When you want to Analize data in Excel you need to arrange it in a Database or Table format first, with headings like EmplName Date TotalInvoices CustmVisits ItemSold Amt Then each row is a transaction record. Once the data is arranged like this you can easily use DataFilterAutofilter to get information about any employee/date/Sales etc and apply commissions to the data or subtotals. The Subtotal function, in fact, has several forms that perform many types of anal on filtered data of this kind. "Rock" wrote: I am importing productivity data from our pos system into worksheet A and trying to use it to sum up some totals per employee and put it in worksheet B to calculate commission. I would like to do a lookup for the employee and then add up his totals for(in this example) air filters and Breathers. So the lookup should return 25.98 for DUMA,ABE or 21.98 for DAVIS,AL. Is this possible with lookups and sums? column A column B Employee: DUMA,ABE Average Time Per Car Item ================== TOTAL INVOICES 7 CUSTOMER VISITS: ****************** LUBE ONLY: ****************** AIR FILTERS: 14.99 BREATHER FILTERS: 10.99 Employee: DAVIS,AL Average Time Per Car Item ================== TOTAL INVOICES 12 CUSTOMER VISITS: ****************** LUBE ONLY: ****************** AIR FILTERS: 7.99 BREATHER FILTERS: 13.99 -- Thanks Roch |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup and sum of multiple lines
Arrange your data as you have shown, EXCEPT: insert headers in ROW 1: Name
and Amt. Then at the bottom of col B, say B10, enter: =SUBTOTAL(9,B2:B7) then click on one of the data cells, say A4, then DataFilterAutoFilter Use the drop-down arrow to select one of the Names. The subtotal formula above will adjust to find the subtotal for the name you choose. "Rock" wrote: If I arrange my data as you say A B Paul 114 george 80 Paul 20 How would I get all the Paul to add up with a VLOOKUP, something like this =VLOOKUP(O12, B1:G3000,SUBTOTAL(9,G1:G3000),FALSE) I know this one doesn't work but am i going in the right direction Thanks "gocush" wrote: When you want to Analize data in Excel you need to arrange it in a Database or Table format first, with headings like EmplName Date TotalInvoices CustmVisits ItemSold Amt Then each row is a transaction record. Once the data is arranged like this you can easily use DataFilterAutofilter to get information about any employee/date/Sales etc and apply commissions to the data or subtotals. The Subtotal function, in fact, has several forms that perform many types of anal on filtered data of this kind. "Rock" wrote: I am importing productivity data from our pos system into worksheet A and trying to use it to sum up some totals per employee and put it in worksheet B to calculate commission. I would like to do a lookup for the employee and then add up his totals for(in this example) air filters and Breathers. So the lookup should return 25.98 for DUMA,ABE or 21.98 for DAVIS,AL. Is this possible with lookups and sums? column A column B Employee: DUMA,ABE Average Time Per Car Item ================== TOTAL INVOICES 7 CUSTOMER VISITS: ****************** LUBE ONLY: ****************** AIR FILTERS: 14.99 BREATHER FILTERS: 10.99 Employee: DAVIS,AL Average Time Per Car Item ================== TOTAL INVOICES 12 CUSTOMER VISITS: ****************** LUBE ONLY: ****************** AIR FILTERS: 7.99 BREATHER FILTERS: 13.99 -- Thanks Roch |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup and sum of multiple lines
If the functions in the file at http://home.pacbell.net/beban are
available to your workbook =SUM(VLOOKUPS("Paul",A1:B3,2)) Alan Beban Rock wrote: If I arrange my data as you say A B Paul 114 george 80 Paul 20 How would I get all the Paul to add up with a VLOOKUP, something like this =VLOOKUP(O12, B1:G3000,SUBTOTAL(9,G1:G3000),FALSE) I know this one doesn't work but am i going in the right direction Thanks "gocush" wrote: When you want to Analize data in Excel you need to arrange it in a Database or Table format first, with headings like EmplName Date TotalInvoices CustmVisits ItemSold Amt Then each row is a transaction record. Once the data is arranged like this you can easily use DataFilterAutofilter to get information about any employee/date/Sales etc and apply commissions to the data or subtotals. The Subtotal function, in fact, has several forms that perform many types of anal on filtered data of this kind. "Rock" wrote: I am importing productivity data from our pos system into worksheet A and trying to use it to sum up some totals per employee and put it in worksheet B to calculate commission. I would like to do a lookup for the employee and then add up his totals for(in this example) air filters and Breathers. So the lookup should return 25.98 for DUMA,ABE or 21.98 for DAVIS,AL. Is this possible with lookups and sums? column A column B Employee: DUMA,ABE Average Time Per Car Item ================== TOTAL INVOICES 7 CUSTOMER VISITS: ****************** LUBE ONLY: ****************** AIR FILTERS: 14.99 BREATHER FILTERS: 10.99 Employee: DAVIS,AL Average Time Per Car Item ================== TOTAL INVOICES 12 CUSTOMER VISITS: ****************** LUBE ONLY: ****************** AIR FILTERS: 7.99 BREATHER FILTERS: 13.99 -- Thanks Roch |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| In Excel 2007 chart with multiple lines, mouse doesn't track lines | Charts and Charting in Excel | |||
| display 1 line of multiple worksheets into multiple lines on 1 wks | Excel Worksheet Functions | |||
| Display multiple lines of text within a cell from multiple column. | Excel Worksheet Functions | |||
| vlookup with multiple lines of same value | Excel Worksheet Functions | |||
| Multiple blank lines, between multiple lines | Excel Programming | |||
Linear Mode
