Tiered Pricing using SumProduct not working

[jamerson23]

I am trying to build a tiered pricing calculator using an array and a sumproduct function, but the numbers are not coming out correctly. I have attached the document, which has the formula - but here is the formula I used: =SUMPRODUCT((H3<$B$5:$F$5)*(H3>$B$4:$F$4)*$B$6:$F$6) + SUMPRODUCT(((H3>$B$5:$F$5)*($B$5:$F$5 - $B$4:$F$4))*$B$6:$F$6)

Once you open the document you can see that the calculations are all wrong, but I can't figure out why....

Here is my table:
Tier 1 Tier 2 Tier 3 Tier 4 Tier 5
Lower Limit 0 25 50 100 200
Upper Limit 25 50 100 200 999999
Price Per ($) 8 7 6 5 8

And here are the results I am getting:
Number of Clients Total Cost
100 $375.00
150 $680.00
200 $675.00
250 $1,183.00
300 $1,183.00
400 $1,183.00
500 $1,183.00

You can see that the answers make no sense!

[FlameRetired]

Using this site for instruction Variable rates and commissions I put this formula in B7:F7 to get the differential rates.

Please Login or Register  to view this content.

and this formula in I3:I9

Please Login or Register  to view this content.

Do these figures look more likely to you?

	A	B	C	D	E	F	G	H	I
2								Number of Clients	Total Cost
3		Tier 1	Tier 2	Tier 3	Tier 4	Tier 5		100	$ 1,075.00
4	Lower Limit	0	25	50	100	200		150	$ 1,675.00
5	Upper Limit	25	50	100	200	999999		200	$ 2,275.00
6	Price Per ($)	8	7	6	5	8		250	$ 3,375.00
7		8	-1	7	-2	10		300	$ 4,475.00
8								400	$ 6,675.00
9								500	$ 8,875.00

Or this instead:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H	I
2								Number of Clients	Total Cost
3		Tier 1	Tier 2	Tier 3	Tier 4	Tier 5		100	$ 550.00
4	Lower Limit	0	25	50	100	200		150	$ 1,250.00
5	Upper Limit	25	50	100	200	999999		200	$ 1,950.00
6	Price Per ($)	8	7	6	5	8		250	$ 2,550.00
7		8	-1	7	-2	10		300	$ 3,150.00
8								400	$ 4,350.00
9								500	$ 5,550.00

[jamerson23]

Hi Dave!

Thank you for taking a crack at this. I didn't think of the differential rate, but I'm still not sure how to make this work. For example:

100 Clients should be $675

(25 * $8) + (25 * $7) + (50 * $6) = $200 + $175 + $300 = $675

Does that make sense?

[Bo_Ry]

Please try at J3

=SUMPRODUCT(TEXT(H3-$B$4:$F$4,"0;\0;0")*($B$6:$F$6-N(+$A$6:$E$6)))

[Phuocam]

1.Insert column B

2. Try this:

=SUMPRODUCT((I3>$C$4:$G$4)*(I3-$C$4:$G$4)*($C$6:$G$6-$B$6:$F$6))

[jamerson23]

Hi Bo_Ry,

I'm not sure how you figured that out, but it completely works! Thank you so so so much.

[FlameRetired]

Please try at J3

=SUMPRODUCT(TEXT(H3-$B$4:$F$4,"0;\0;0")*($B$6:$F$6-N(+$A$6:$E$6)))

Great formula.

Please tell us (me ) where do you find these specialized formatting strings? TEXT(H3-$B$4:$F$4,"0;\0;0") I've searched the web and have not found help beyond those in the Excel help articles. They don't include this one.

[Bo_Ry]

Dave
Thanks for the reps

The first time I saw this was from BOSCO.
You may refer to https://www.ablebits.com/office-addi...number-format/
\ for Displays the character that follows it.
And combine with POSITIVE; NEGATIVE; ZERO; TEXT

[FlameRetired]

Thank you. I bookmarked that one and will be studying it in the days to come.