Microsoft Office Forums sumproduct??

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-09-2012, 02:52 PM
jer jer is offline sumproduct?? Windows 7 32bit sumproduct?? Office 2007
Novice
sumproduct??
 
Join Date: Aug 2011
Posts: 9
jer is on a distinguished road
Default 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) ....
Reply With Quote
  #2  
Old 10-12-2012, 11:47 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline sumproduct?? Windows 7 32bit sumproduct?? Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

If you re-arrange your From-To 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/...-with-vlookup/

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.
Reply With Quote
  #3  
Old 10-13-2012, 12:54 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline sumproduct?? Windows 7 64bit sumproduct?? Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,397
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

A solution using SUMPRODUCT
Attached Files
File Type: xls Sumprod.xls (7.0 KB, 5 views)
__________________
Please note I am not active on this forum anymore
Reply With Quote
  #4  
Old 10-14-2012, 07:41 AM
jer jer is offline sumproduct?? Windows 7 32bit sumproduct?? Office 2007
Novice
sumproduct??
 
Join Date: Aug 2011
Posts: 9
jer is on a distinguished road
Default

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 re-arranging 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
Reply With Quote
  #5  
Old 10-14-2012, 07:56 AM
jer jer is offline sumproduct?? Windows 7 32bit sumproduct?? Office 2007
Novice
sumproduct??
 
Join Date: Aug 2011
Posts: 9
jer is on a distinguished road
Default sumproduct??

p.s. here is sample file
Attached Files
File Type: xlsx Book1.xlsx (8.2 KB, 4 views)
Reply With Quote
  #6  
Old 10-14-2012, 08:32 AM
jer jer is offline sumproduct?? Windows 7 32bit sumproduct?? Office 2007
Novice
sumproduct??
 
Join Date: Aug 2011
Posts: 9
jer is on a distinguished road
Default

Colin tried your vlookup again and it works, thanks, but would really like to calculate charges in one go
thanks again
Reply With Quote
  #7  
Old 10-14-2012, 08:33 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline sumproduct?? Windows 7 32bit sumproduct?? Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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
Reply With Quote
  #8  
Old 10-14-2012, 09:23 AM
jer jer is offline sumproduct?? Windows 7 32bit sumproduct?? Office 2007
Novice
sumproduct??
 
Join Date: Aug 2011
Posts: 9
jer is on a distinguished road
Default

thanks again Colin
Reply With Quote
  #9  
Old 10-14-2012, 09:32 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline sumproduct?? Windows 7 64bit sumproduct?? Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,397
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

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
Reply With Quote
  #10  
Old 10-14-2012, 10:00 AM
jer jer is offline sumproduct?? Windows 7 32bit sumproduct?? Office 2007
Novice
sumproduct??
 
Join Date: Aug 2011
Posts: 9
jer is on a distinguished road
Default

my apologies Pecoflyer, your understanding is correct now
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
sumproduct?? Sumproduct formula Portucale Excel 2 09-12-2012 10:51 AM
Match Index with sumproduct/vlookup angie.chang Excel 1 06-18-2012 08:47 AM
sumproduct?? Sumproduct angie.chang Excel 3 06-14-2012 10:00 AM
sumproduct?? sumproduct formula to pull info from multiple sheets Berk21 Excel 7 01-15-2012 11:41 AM


All times are GMT -7. The time now is 10:32 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft