Formula to calculate pricing per row after total goes over threshhold

[o1darcie1o]

Sorry this is so long!

I'm trying to calculate what to charge a customer based on their reporting to us.

Here's the scenario:
We're billing the customer a flat fee for up to a certain number of users and once they go over it, there's tiered pricing.
Customer reports to us what subscribers they've given our service to, and how many users each subscriber has.
Each subscriber can have no more than 150 users.

The first 1,250 total users are included in the initial flat fee. Once the total goes over the 1,250, the below tiers apply per subscriber:
1-25 @ $475.00
26-50 @ $425.00
51-100 @ $778.00
101-150** @ $235.00
(If a subscriber has 2 users or 24, the charge is $475. If they have 45, the charge is $900 (first tier plus second). Etc.


For the fees, I need add up the total users from the top to the current row - there's no charge if the total is less than 1250, if it's more than 1250 I need to charge based on the tiers EXCEPT for the first subscriber that puts the total over 1,250 because I only need to charge for the number of users above the 1250.
When I originally built my formula, it was with the assumption that only subscribers with users would be reported, so every row would have a calculation, but they threw me off by reporting 0's.
(As a side note, there are two services involved in this, and I'm calculating the second. So there are rows for every subscriber, but some only have the first service and are reported with 0 for the second.)

I've got the partner names in column A, the number of reported users in B, and the fee calculation in C.
I created a second tab (called Pricing) with the pricing where column A is the number of users (it's only up to 150 so it's just a list of numbers) with column B as the charge for that number of users. The first row of this is 0 users for 0 charge.

Here's my formula:
=IF(B3>150,"too many users",
IF(and(C2<>"",C2<>"too many users"),VLOOKUP(B3,Pricing!$A$2:$B$152,2,0),
IF(SUM(B$2:B3)>1250,VLOOKUP(SUM(B$3:B3)-1250,Pricing!$A$2:$B$152,2,0),""
)))

This formula worked fine when I expected every row to have a number of users - the row where it went over 1250 would be calculated, then the formula checks the row above and if it's not blank, lookup the number of users reported. But now the not blank doesn't work because of the reported zero's.

I'm not opposed to using a helper column if needed, but I'm giving this to the customer to use for their reporting, so I'm hoping to keep those to a minimum.
I'm working in Excel 2013, but copied to a google sheet for sharing:

Per Subscriber Tiers

docs.google.com

I put 1200 as example 7 to show what's happening at the bottom of the sheet.

 

[dave3009]

Try like this

=IF(B2,SUM(Pricing!$B$2:INDEX(Pricing!$B$2:$B$5,MATCH(B2,Pricing!$A$2:$A$5,1))),0)

You can add a clause to handle more than 150 if you want, this will handle any number of subscribers.

Book1
	A	B	C
1	From	To	Tier
2	1	25	475
3	26	50	425
4	51	100	778
5	101	150	235
Pricing

Book1
	A	B	C
1	Subscriber	Users	Fee
2	Example 1	0	0
3	Example 2	0	0
4	Example 3	0	0
5	Example 4	0	0
6	Example 5	0	0
7	Example 6	0	0
8	Example 7	1200	1913
9	Example 8	20	475
10	Example 9	0	0
11	Example 10	0	0
12	Example 11	1	475
13	Example 12	0	0
14	Example 13	0	0
15	Example 14	0	0
16	Example 15	18	475
17	Example 16	0	0
18	Example 17	13	475
19	Example 18	0	0
20	Example 19	0	0
Sheet1

Cell Formulas
Range	Formula
C2:C20	C2	=IF(B2,SUM(Pricing!$C$2:INDEX(Pricing!$C$2:$C$5,MATCH(B2,Pricing!$A$2:$A$5,1))),0)

 

[o1darcie1o]

Hi Dave,
That didn't work - it added pricing for every example not listed as 0, but I only need pricing added for users 1251 and above.
The dollar amount for the users, is correct though, so thank you!

 

[Dave Patton]

What is the fee for 1500?
Do you require the total or are you completing a form for each tier?