# Billing different amounts based on counting the total number purchased

#### mark the excel novice

##### New Member
I'm looking to bill different amounts based on how many "movies" have been delivered.

Here is my spreadsheet. The billing should differ based on the number of "movies" delivered. The first 5 movies should be billed at £1.50 base rate and 15p per minute for the duration of the asset. Movies 6 -10 should be billed at £1.50 base rate and 10p per minute for the duration of the asset.

So, Column A is confirming which assets are movies and which are subs. Column B shows the duration of the asset. These columns have no formulas.

Column C is where think my solution might be for counting how many movies we have delivered. Currently I cannot figure out the formula to achieve what is in the screenshot, a count of all the movies in Column A. My thoughts would be that the formula in Column G would change to say something like **- IF C2 is equal to or greater than 6 use J3, IF C2 is less than 6 use J2.**

Columns D & E are just =COUNTIF(A2,"SUBTITLE") & =COUNTIF(A2,"MOVIE")

Column F calculates the duration in Column B as just minutes (from timecode) =IF(AND(A2="MOVIE"),(LEFT(B2,2)*60+MID(B2,4,2)+IF(MID(B2,7,2)+0<30,0,1)),"0")

Column G is where i'm calculating the cost per asset. Maybe my formula could be altered to not need to include a Column C calculation like i suggested above, if there is a cleverer way to achieve the outcome.

=IFERROR(IF(E2>0,\$J\$2*F2+5.6,IF(D2>0,\$J\$5*D2,"£0.00")),"£0.00") Currently its seeing if a movie or a sub has been found in E or D, then its adding the base rate to a calculation of the number of minutes * J2.

J2 being .15p per minute. My aim is to make a formula clever enough to change J2 to J3 once the number of movies exceeds 5.

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### JamesCanale

##### Active Member
I'm not getting the same results as you, but here is my go at it. The structure looks good to me.
MrExcelPlayground2.xlsm
ABCDEFGHIJ
1AssetDurationCountSubMovieMinutesCostBilling
2MOVIE00:42:00.0610142\$7.80one-five\$0.15
3SUBTITLE00:32:00.07 100\$0.10six-ten\$0.10
4MOVIE01:42:00.08201102\$16.80per movie\$1.50
5MOVIE02:00:00.09301120\$19.50subs\$0.10
6SUBTITLE00:42:00.10 100\$0.10
7SUBTITLE00:39:03.15 100\$0.10
8SUBTITLE00:40:19.01 100\$0.10
9MOVIE00:40:19.0140140\$7.55
10MOVIE00:40:19.0150140\$7.55
11MOVIE00:40:19.0160140\$5.53
12MOVIE00:41:52.1070142\$5.69
13SUBTITLE00:41:52.10 100\$0.10
14SUBTITLE00:41:52.10 100\$0.10
15MOVIE00:42:00.1980142\$5.70
16MOVIE00:42:00.2090142\$5.70
Sheet38
Cell Formulas
RangeFormula
C2C2=IF(A2="MOVIE",1,0)
D2:D16D2=IF(A2="SUBTITLE",1,0)
E2:E16E2=IF(A2="MOVIE",1,0)
F2:F16F2=IF(A2="MOVIE",B2*24*60,0)
G2:G16G2=IF(A2="MOVIE",IF(C2<6,F2*\$J\$2+\$J\$4,F2*\$J\$3+\$J\$4),\$J\$5)
C3:C16C3=IF(A3="MOVIE",MAX(C\$2:C2)+1,"")

#### mark the excel novice

##### New Member
Thank you so much James. I would never have figured out the count formulas in C2 then C3 would differ! The reason our G columns had differing results is because I actually bodged my formula to add \$5.60 to the cost instead of \$1.50. Other than my mistake it all adds up.

Appreciate your time and help, this will save me manually changing the cost in future on multiple spreadsheets.

Replies
14
Views
266
Replies
13
Views
292
Replies
3
Views
120
Replies
3
Views
155
Replies
8
Views
203

1,141,816
Messages
5,708,747
Members
421,588
Latest member
Wawie

### 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