# Mobile Phone Bill

#### NTZ

##### New Member
Hi, my boss has asked me to look at our mobile/cell phone bill. We should recieve three thousand minutes of free air time each month. The duration of the calls are marked onthe bill in hrs.min.sec e.gif a call lasted 8mins and 43sec then it shows as 00:08:43.
This information is all in one column how do I add the figures in the column to give me a total at the bottom that displays the total number of free hours, mins, sec we have recieved.

Many Thanks

NTZ

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Try:

=IF(SUM(A1:A100)>2.08333333,2.0833333,SUM(A1:A100))

2.08333 is Excel's interpretation of 3000 min (or 50 hrs) or 2.08333 days

You may have to format result as Time.

Maybe

=MAX(3000/(24*60),SUM(A1:A100))

just_jon said:
Maybe

=MAX(3000/(24*60),SUM(A1:A100))

Hi JJ,
Doesnt the OP want MIN not MAX here?

just_jon said:
Maybe

=MAX(3000/(24*60),SUM(A1:A100))

My interpretation of the OPs question is to find out how many free minutes were used in the month.

Your formula returns 3000 minutes always (which is not necessarily how much time they used up), unless the sum(a1:a100) is more than 3000. But then those extra minutes are not free minutes anymore.

Maybe =MIN(3000/(24*60),SUM(A1:A100))

Hi thanks for the replies, I haven't explained myself very well. I have filtered the bill by the call charge so the column I am left with is the calls that are marked as free. So I am just adding these times together to give me the total number of free hour,mins and secs.

Thanks NTZ

Then just use =Sum(A1:A100) formatted as Time:

Actually,

For your filtered case (if you used DATA|FILTER|AUTOFILTER), better to use

=SUBTOTAL(9,A1:A100). This will sum everything after the filtering (ie. not include hidden values).

al_b_cnu said:
just_jon said:
Maybe

=MIN(3000/(24*60),SUM(A1:A100))

Hi JJ,
Doesnt the OP want MIN not MAX here?

Absolutely, need coffee !

NTZ said:
Hi thanks for the replies, I haven't explained myself very well. I have filtered the bill by the call charge so the column I am left with is the calls that are marked as free. So I am just adding these times together to give me the total number of free hour,mins and secs.

Thanks NTZ

Can also use SUMIF

=SUMIF(FreeColumn,"Free",MinutesColumn)

Replies
6
Views
1K
Replies
0
Views
809
Replies
5
Views
747

1,203,681
Messages
6,056,708
Members
444,885
Latest member
Mark Prillman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back