vlookup and sum of multiple lines

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 Data>Filter>Autofilter 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.
 
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
 
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 Data>Filter>AutoFilter
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.
 
Back
Top