Extract Talk Time to Mobile Phones

[Alan L 185]

Hi

I’m trying to develop a spreadsheet that will analyse phone call data made from my school’s extension number and school mobile phones (Caller Number) to mobile phone numbers (Callee Number).
I’ve created two worksheets to analyse the call data. The first worksheet is called “Call Data” and the second is “Call Report”.

Call Data Worksheet
The data will be pasted into Columns A (Caller Number), B (Callee Number) and C (Talk Time (Seconds)) in the Call Data Worksheet. I’ve inserted a formula in to Column D (Talk Time (Decimal Minutes) to convert the seconds into decimal minutes. I’ve also inserted a formula in to Column E that rounds up the decimal minutes in Column D to the nearest minute.

Call Report Worksheet
I need a formula in the Column C (Total Talk Time to Mobile Numbers) in the Call Report Worksheet that will add up all rounded-up minutes (Column E of the Call Data worksheet) for each Caller Number (Column A of the Call Report worksheet) to mobile phone numbers only (i.e. the numbers that are 8-10 digit long and starting with any one of the following: 20…, 21…, 22…, 23…, 24…, 25…, 26…, 27…, 28…, 29…) in Column B of the Call Data worksheet.
Please note: I have changed the actual phone numbers for privacy reasons.


Thanks in advance.

Alan L

[JohnTopley]

Look at SUMIF/SuMIFS functions

[Alan L 185]

Hi

I tried to use a SUMIFS with OR logic but I can't get the formula correct.

=SUM(SUMIFS('Call Data'!E:E,'Call Data'!B:B{"20*","21*","22*","23*","24*","25*","26*","27*","28*","29*"}))

Can someone please help me with the correct formula?

[AliGW]

You missed a comma:

=SUM(SUMIFS('Call Data'!E:E,'Call Data'!B:B,{"20*","21*","22*","23*","24*","25*","26*","27*","28*","29*"}))

[JohnTopley]

This will give a total of ALL numbers

=SUM(SUMIFS('Call Data'!E:E,'Call Data'!B:B,{"20*","21*","22*","23*","24*","25*","26*","27*","28*","29*"}))

To select mobile numbers, I checked number length > 8 and checked the number prefix

=IF(AND(LEN(A2)>=8,LEFT(A2,2)>="20",LEFT(A2,2)<="29"),SUMIFS('Call Data'!E:E,'Call Data'!A:A,A2),"")

[Alan L 185]

Hi Ali

Thanks for your correct. You must have a good eye for detail.

I’m very much an amateur at this and unfortunately, the formula I developed doesn’t do what I want it to do. It looks like it totals up for all mobile phone as it gives the same total for all ‘Caller Numbers’ of 1881.
I need a formula in the Column C (Total Talk Time to Mobile Numbers) in the Call Report Worksheet that will add up all rounded-up minutes (Column E of the Call Data worksheet) for each Caller Number (Column A of the Call Report worksheet) to mobile phone numbers only (i.e. the numbers that are 8-10 digit long and starting with any one of the following: 20…, 21…, 22…, 23…, 24…, 25…, 26…, 27…, 28…, 29…) in Column B of the Call Data worksheet.

Alan L

[Alan L 185]

Hi John

Thanks for the better formula than I developed. I’ve pasted it in to cell C2 on the Call Report worksheet, but can’t get it to add up all rounded-up minutes (Column E of the Call Data worksheet) for each Caller Number (Column A of the Call Report worksheet) to mobile phone numbers. See the attached spreadsheet.

Can you please help me?

Alan L

[JohnTopley]

=SUMPRODUCT(('Call Data'!E2:E8754),(LEN('Call Data'!A2:A8754)>=8)*(LEFT('Call Data'!A2:A8754,1)="2"))

or

=SUM(SUMPRODUCT(('Call Data'!E2:E8754),(LEN('Call Data'!A2:A8754)>=8)*(LEFT('Call Data'!A2:A8754,2)>="20")*(LEFT('Call Data'!A2:A8754,2)<="29")))

Sums all mobiles

[Alan L 185]

Hi John

Thanks for your prompt response. I’ve copied and pasted you first new formula into Column C of the Call Report worksheet and filled down.

Unfortunately, all figures are the same. I’ve attached the adjusted spreadsheet to show you.


Alan L

[JohnTopley]

You need to copy down the original formula I gave in post #5

[Alan L 185]

Sorry, John I'm confused.

Could you please send it to me in the spreadsheet I uploaded?

[JohnTopley]

See the attached

[davsth]

I interpret the request differently to the above. I take it that mobile numbers on the data sheet need to be summed for each person on the report sheet. Each person needs to be monitored for phoning mobile numbers.
=SUMPRODUCT((LEN('Call Data'!$B$2:$B$8755)>8)*(LEFT('Call Data'!$B$2:$B$8755,2)>="20")*(LEFT('Call Data'!$B$2:$B$8755,2)<="29")*('Call Data'!$E$2:$E$8755)*('Call Data'!$A$2:$A$8755=A59))

although this would take a long time computationally to be replicated for each number.

perhaps add a helper column to the data sheet =AND(LEN(B2)>=8,LEFT(B2,2)>="20",LEFT(B2,2)<="29") and copy down, and then perform a pivot on the data filtering for this would be quicker

[Alan L 185]

Hi

Yes davsth, your interpretation of what I need is correct. I want a list of the 'talk time' made from school landline and mobile phones to monile phones. My apology for causing confusion.

I've added a third worksheet (Call Report (Manual Example)), where I've manually calculated the total talk time made from school landline and mobile phones to moblile phone numbers. I hope this helps to clarify what I need.

Could you please help me by changing the attached worksheet so that it performs the calculations automatically?

Alan L

[Alan L 185]

Sorry, I can't upload the adjusted file.

[AliGW]

What’s the problem with uploading the workbook?

[Alan L 185]

Hi

It was a file size problem. I've zipped the file and uploaded it.


Alan L

[JohnTopley]

See attached using formula from Davsth: spot on!!!

Interpretation of your request was my fault not yours:

[Alan L 185]

Thank you very much. It works great.

All the best.

Alan L