Excel: creating phone bill totals

[jaslr]

I am not sure what terms to use as I am quite new to Microsoft Excel. My company gets quite a big phone bill and I need to categories the calls through excel.

I am able to get a phone list of my phone company's website and paste it into excel.

At this stage I am used sort so each dialed number is grouped and a sub-total is calcuated. Then I had staff go through and pick out what numbers they called and I now know who each number was and who dialed it.

So, now that I have this data how can I?: Create an excel 'form' (i guess) where when my next bill comes I can paste the list in and press "Sort into who dialed the number" and each phone number and cost will be sorted so there's a total for: Accounting, IT and HR ?

I just need somewhere to start, not someone to tell me EXACTLY what to do, even though that would be great

Thanks a lot for reading this.

 

[Bowers74]

You could try the SUMIF() function for this or the SUMPRODUCT() function for more than one criteria (i.e. Calls per Phone Number per Month)

Show me an example of your data so that I can give you a better answer.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[KenWright]

You can sort the data as you have already done, then hopefully you used the Data / Subtotals feature instead of subtotalling manually (This is your Detail List). This will also allow you to collapse the data so that you just see the grouped totals instead of all the detail, at the click of button. You can then also set up a list of phone numbers and owners from your last round as a separate table (Your master list), and then finally you can then use VLOOKUP to reference the phone numbers in your new grouped Detail List against the Master List. This will probably entail about 5-10 minutes work each time once you have it set up first time round.

As you get new numbers that are not recognised, you will simply have to find out who owns them, and then add them to your master list.

Example,

Sheet1
Detail List (Grouped, with costs subtotalled)
A B C
1 1234567 £150 =VLOOKUP(A2,Sheet2!$A$1:$B$4,2,0)
2 2345678 £200 =VLOOKUP(A3,Sheet2!$A$1:$B$4,2,0)
3 3456789 £125 =VLOOKUP(A4,Sheet2!$A$1:$B$4,2,0)
4 4567890 £235 =VLOOKUP(A5,Sheet2!$A$1:$B$4,2,0)

You can simply paste the formula into cell C1 and then copy down using the Autofill handle.

Sheet2
Master List
A B
1 1234567 John Smith
2 2345678 Fred Bloggs
3 3456789 Norman Wisdom
4 4567890 Alice Cooper

If you have your two sets of data, and are able to mail it out, then I will happily set it up for you to show you how.

ken.wright at ntlworld.com

Regards
Ken..................

 

[jaslr]

thank you ken, I will try this now and if I have problems I will mail you the list.

 

[jaslr]

OK, here is sheet 1 (Phone bill - test data):
A B
4324 $5
43214 $5
4324 $5
43214 $5
43214990 $5
43241990 $5
43214 $5
4324 $5
43214 $5
43214990 $5
43241990 $5
43214 $5
43214990 $5
43241990 $5
559403 $5
43214 $5
43214990 $5
43241990 $5
66793 $5
43214 $5
43214990 $5
43241990 $5


Here is sheet 2: (Master sheet - list of who calls what numbers)

A B
4324 Steve
66793 Steve
43214 Ian
43214990 Liam
559403 Liam
43241990 Cathy


_____________
As you can see, I want all the calls that Steve made under 1 heading with the call total in $ and vice versa for the other people.

I understand that I need a vlookup, lookup or hlookup in each column C cell in sheet 1, but I am not sure what exactly to put in.

I know then that after I have sorted them into who "Call Owner" then I can easily use sort to create totals.

 

[Bowers74]

Cell C in Sheet 1 = =VLOOKUP($A1,Sheet2!$A$1:$B$22,2,0) and copy down the sheet.

That will add the employee's name to the "Calls" list and then:

Cell C1 in Sheet 2 = =SUMPRODUCT((Sheet1!$A$1:$A$22=$A1)*(Sheet1!$A$1:$A$22)) and copy down the sheet.

That will give you the total costs for each number in your master list, and then:

In cell D1 in sheet 2 = COUNTIF(Sheet1!$A$1:$A$22,$A1) and copy down the sheet.

That will give you the total number of calls made to that particular number, and (if you want to) then.

Create a Sheet 3 and list the employees from A1:A4 and enter into cell B1: =SUMPRODUCT((Sheet1!$B$1:$B$22=$A1)*(Sheet1!$A$1:$A$22)) and copy down the sheet.

That will give you the total costs for the calls for each employee then:

In cell C1 in sheet 3 = COUNTIF(Sheet1!$B$1:$B$22,$A1)

That will give you the total number of calls made by the employee.

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[SkipVought]

Hi,

I would use a PivotTable to count phone number occurrences each month.

Here's the structure.

Each month you paste the list.
You have a new column for Date. When you pivot on Month, you can Group & Outline and select to group by Month & Year
You also have a lookup column for the Name...

=INDEX(Sheet2NameRange,MATCH(Sheet1NbrFromPastedList,Sheet2NbrList,0),1)

Your PivotTable could list Name and Number in the ROW AREA and Date in the COLUMN AREA and Count of Number in the DATA AREA.

Each month you will see the counts as they accumulate. You could even do a PivotChart and get a graphic view.

Hope this adds to the suitable alternatives

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Bowers74]

Good point Skip,

Pivot Tables are great, I tend to over look them because my boss doesn't like to use them. :-(

I have gotten used to working around that by using solutions like the one above.

(me) (my boss)

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[SkipVought]

Throw him a bomb from me, Mike

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jaslr]

woohoo, got it workoing following exactly Bowers74 instructions. thanks a lot

 

[SkipVought]

jaslr,

Don't forget to give Bowers74 a STAR!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[y2ktiger]

This is for Bowers74 or jaslr,
Hi Guys,
Sometime ago I remember reading about keeping track of your telephone bills and the employees, after quiet a while I finally found this thread
I have followed Bowers formulas to the 'T' and have created the worksheet.
What I would like to learn is how to use it, including posting of new phone bills, hope you will oblige.
Thanks
Tiger.