Billing different amounts based on counting the total number purchased

[mark the excel novice]

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.

 

[JamesCanale]

I'm not getting the same results as you, but here is my go at it. The structure looks good to me.

MrExcelPlayground2.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Asset	Duration	Count	Sub	Movie	Minutes	Cost		Billing
2	MOVIE	00:42:00.06	1	0	1	42	$7.80		one-five	$0.15
3	SUBTITLE	00:32:00.07		1	0	0	$0.10		six-ten	$0.10
4	MOVIE	01:42:00.08	2	0	1	102	$16.80		per movie	$1.50
5	MOVIE	02:00:00.09	3	0	1	120	$19.50		subs	$0.10
6	SUBTITLE	00:42:00.10		1	0	0	$0.10
7	SUBTITLE	00:39:03.15		1	0	0	$0.10
8	SUBTITLE	00:40:19.01		1	0	0	$0.10
9	MOVIE	00:40:19.01	4	0	1	40	$7.55
10	MOVIE	00:40:19.01	5	0	1	40	$7.55
11	MOVIE	00:40:19.01	6	0	1	40	$5.53
12	MOVIE	00:41:52.10	7	0	1	42	$5.69
13	SUBTITLE	00:41:52.10		1	0	0	$0.10
14	SUBTITLE	00:41:52.10		1	0	0	$0.10
15	MOVIE	00:42:00.19	8	0	1	42	$5.70
16	MOVIE	00:42:00.20	9	0	1	42	$5.70
Sheet38

Cell Formulas
Range	Formula
C2	C2	=IF(A2="MOVIE",1,0)
D2:D16	D2	=IF(A2="SUBTITLE",1,0)
E2:E16	E2	=IF(A2="MOVIE",1,0)
F2:F16	F2	=IF(A2="MOVIE",B2*24*60,0)
G2:G16	G2	=IF(A2="MOVIE",IF(C2<6,F2*$J$2+$J$4,F2*$J$3+$J$4),$J$5)
C3:C16	C3	=IF(A3="MOVIE",MAX(C$2:C2)+1,"")

 

[mark the excel novice]

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.