Problem using SUMPRODUCT

[Nafty]

Hi all, I have been trying to create a workbook that calculates the cost of documents being supplied. The sliding charging schedule I would be using looks like below:


Start End Rate
0 19 1.75 1-19 pages
20 50 1.45 20-50 pages
51 and over 1.00 51 or more

In the attached it works all OK up to 20 pages. In the example I have made all rates $1.00. When I enter 21 documents, it gives the answer as $22.00, when it should be $21.00.

Any help on what I have done wrong would be greatly appreciated.

Thanks

Graeme

[FDibbins]

Hi and welcome to the forum

how about just...
=A2*VLOOKUP(A2,L6:N8,3,1)

[samba_ravi]

=sumproduct((a2>=l6:l8)*(a2<=m6:m8)*(n6:n8))*a2

[Nafty]

Thanks for the prompt reply. Have added that to the example but still can't get it right. I have updated the charge rates to the real rates.

Using the Vlookup its right up to 19 pages with a total of $33.25. Change the the page total to 20 pages and the result then goes to $29.00, when it should be $34.70.

Thanks

Graeme

[ramananhrm]

The cell marked in green will give you the final answer.

Please try this file.

[Debraj Roy]

Hi nafty..

provide formula is for threshold calculation..

please set you data table as below..

Formula will work fine for you..

Data Range

	L	M	N
5	Start	End	Rate
6	0	19	1.75
7	19	49	-0.30
8	49	and over	-0.45

[FDibbins]

OK i see what you want now....

=IF(A2>19,33.25+VLOOKUP(A2,L6:N8,3,1)*(A2-19),IF(A2>50,33.25+72.5+VLOOKUP(A2,L6:N8,3,1)*(A2-50),VLOOKUP(A2,L6:N8,3,1)*A2))
(messy, I know, sorry)

[Nafty]

Thanks all for the great responses. Got it working now.

Thanks

Graeme

[Fotis1991]

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[samba_ravi]

Use this array formula
=sumproduct((a2>=l6:l8)*(n6:n8)*index(if(a2>=m6:m8,(m6:m8-l6:l8+1),a2-l6:l8+1),0))

[FDibbins]

Happy to help and thanks for the feedback