tracking cell phone usage


B

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)?
 
Ad

Advertisements

G

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top