tracking cell phone usage

[brad.simmons]

my workbook has two worksheets. sheet 1 column A is a list of phone
numbers, column B is a category (work/personal), column C is a name
(mom, boss). sheet 2 is a list of my cell phone usage with column A
having the number called and column B is the length of call.

how can i calculate the total minutes for each category (work/personal)?

 

[Guest]

The best answer would be to import the two sheets into Access and build a
query. Assuming you don't have Access or you just have to have this in
Excel, here's probably the easiest (and most flexible) answer:

1. Add a column to Sheet 2 titled "Category".
2. For in the first row, enter the following formula (update as needed):

=VLOOKUP(Sheet 2 phone number to look up, Sheet 1 table of numbers and names
and categories, 1 or 2 or 3 whichever corresponds to the phone number column
on sheet 1, FALSE [false tells it to make an exact match])

So it would look something like:

=VLOOKUP(B3,Sheet1!$B$3:$D$4,2,FALSE)

This will bring the category to your data sheet (sheet 2).

NOTE: Make sure you put in the dollar signs ($) on the row numbers to make
them absolute.

3. Copy the formula down the sheet for every record of data.

4. Select any cell of the data records and choose DATA | PIVOT TABLE AND...
and create a pivot table of your results. Put the categories as your
columns, the phone number as the rows and the duration in the data area. It
will by default sum the durations.