Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
tracking cell phone usage
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)? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
tracking cell phone usage
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. " wrote: 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)? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct issues | Excel Worksheet Functions | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions |