#1




sumproduct??
hi all, i am not familar with this function however my reading up here and other forums suggest that it might work for what i want, would really appreciate any help. from the below table, if volume = 2549 then charge =2549 * 2.50, will sumproduct work in this instance, if not can you suggest any other function. my first array in the sumproduct is A9<($C$2:$C$6) and i see false, false, true, true, true. i am stuck with the second array to identify 2.5 and then multiply 2549 by 2.50. hope this is clear
From To charge 0 500 3.50 501 999 3.00 1000 2999 2.50 3000 4999 2.00 5000 999999999 1.50 amount to be charged = 2549, let say in A9 , =SUMPRODUCT(A9<$C$2:$C$6) .... 
#2




If you rearrange your FromTo charge table slightly you can use an approximate match to find the correct charge. Have a look at Example 1 here:
http://colinlegg.wordpress.com/2012/...withvlookup/ That example looks up grades by percentage, but it's the same principle. The only reason to use SUMPRODUCT (or equivalent) would be if you want to calculate multiple charges and sum them in one go. 
#3




A solution using SUMPRODUCT
__________________
Please note I am not active on this forum anymore 
#4




thanks Colin, Pecoflyer for your time and suggestions/feedback.
Colin, yes i want to calculate charges in one, that is, i want to multiply the total in A9 by the associated charge, in the example 2.50. Approximate match does not return correct charge. Not clear on what you mean by rearranging the order. I tried ascending and descending and neither works Pecoflyer, thanks however formula is not giving the correct answer, so if a9 = 2549, from the charge table it would be 2549*2.5. From To charge 0 500 3.50 501 999 3.00 1000 2999 2.50 3000 4999 2.00 5000 999999999 1.50 
#5




sumproduct??
p.s. here is sample file

#6




Colin tried your vlookup again and it works, thanks, but would really like to calculate charges in one go
thanks again 
#7




You don't need the "To" column, so delete it in your example file. Then you can use either of these formulas:
=VLOOKUP(A9,A2:B6,2)*A9 =LOOKUP(A9,A2:A6,B2:B6)*A9 
#8




thanks again Colin

#9




I f i understand correctly, your question was poorly stated.
It is not 3,5 on the first interval + 3 on the second interval (etc..) but 3 starting from 0 if the number is between 500 and 999, etc... Which is, of course a totally different approach
__________________
Please note I am not active on this forum anymore 
#10




my apologies Pecoflyer, your understanding is correct now

Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Sumproduct formula  Portucale  Excel  2  09122012 10:51 AM 
Match Index with sumproduct/vlookup  angie.chang  Excel  1  06182012 08:47 AM 
Sumproduct  angie.chang  Excel  3  06142012 10:00 AM 
sumproduct formula to pull info from multiple sheets  Berk21  Excel  7  01152012 11:41 AM 