Billing different amounts based on counting the total number purchased

Joined
Jul 6, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I'm looking to bill different amounts based on how many "movies" have been delivered.
1625562765103.png


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
Joined
Jan 13, 2021
Messages
295
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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,"")
 
Solution
Joined
Jul 6, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top