Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding amounts that fall within a range
I need a formula that will add amounts in Column B if it falls within certain range in Column C. If Column C is greater than 725, then add amount in Column B (answer will be $47). If Column C is greater than 600, but less than 725, then add amount in Column B (answer will be $45), and so. Ex: A B C Joe $10 655 Jim $20 745 Tom $25 550 Sam $27 738 Amy $35 627 -- pdgaustintexas ------------------------------------------------------------------------ pdgaustintexas's Profile: http://www.excelforum.com/member.php...o&userid=30600 View this thread: http://www.excelforum.com/showthread...hreadid=503537 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding amounts that fall within a range
try
=SUMIF(J:J,""&725,K:K) =SUMPRODUCT((J2:J22725)*I2:I22) =SUMPRODUCT((J2:J22600)*(J2:J22<725)*I2:I22) -- Don Guillett SalesAid Software "pdgaustintexas" <pdgaustintexas.21yg0m_1137796806.7659@excelforu m-nospam.com wrote in message news:pdgaustintexas.21yg0m_1137796806.7659@excelfo rum-nospam.com... I need a formula that will add amounts in Column B if it falls within certain range in Column C. If Column C is greater than 725, then add amount in Column B (answer will be $47). If Column C is greater than 600, but less than 725, then add amount in Column B (answer will be $45), and so. Ex: A B C Joe $10 655 Jim $20 745 Tom $25 550 Sam $27 738 Amy $35 627 -- pdgaustintexas ------------------------------------------------------------------------ pdgaustintexas's Profile: http://www.excelforum.com/member.php...o&userid=30600 View this thread: http://www.excelforum.com/showthread...hreadid=503537 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding amounts that fall within a range
Hi Texan:
If Column C is greater than 725, then add amount in Column B (answer will be $47). =SUMPRODUCT(--(C1:C100725), B1;B100) =SUMPRODUCT(--(C1:C100D1), B1;B100) 'D1 has value 725 =SUMIF(C1:C100,"725",B1:B5) If Column C is greater than 600, but less than 725, then add amount in Column B (answer will be $45), and so =SUMPRODUCT(--(C1:C100600),--(C1:C100<725),B1:B100) =SUMIF(C1:C100,"600",B1:B100) - SUMIF(C1:C100,"=725",B1:B100) You cannot reference an entire column such as A:A in Sumproduct For details visit http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "pdgaustintexas" <pdgaustintexas.21yg0m_1137796806.7659@excelforu m-nospam.com wrote in message news:pdgaustintexas.21yg0m_1137796806.7659@excelfo rum-nospam.com... I need a formula that will add amounts in Column B if it falls within certain range in Column C. If Column C is greater than 725, then add amount in Column B (answer will be $47). If Column C is greater than 600, but less than 725, then add amount in Column B (answer will be $45), and so. Ex: A B C Joe $10 655 Jim $20 745 Tom $25 550 Sam $27 738 Amy $35 627 -- pdgaustintexas ------------------------------------------------------------------------ pdgaustintexas's Profile: http://www.excelforum.com/member.php...o&userid=30600 View this thread: http://www.excelforum.com/showthread...hreadid=503537 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding amounts that fall within a range
Hi pdgaustintexas,
If you have to test with a lot of upper and lower limits of the Column C value you could type the upper limit in D1 and the lower limit in D2 and change Sandy's formulas to... =SUMIF(C1:C5,""&$D$1,B1:B5) =SUMIF(C1:C5,"<="&$D$1,B1:B5)-SUMIF(C1:C5,"<"&$D$2,B1:B5) Ken Johnson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding amounts that fall within a range
Hi,
You may want to try the following array formula (Ctrl+Shift+Enter). =sum(if(c1:c5725,b1:b5)) =sum(if((c1:c5600)*(c1:c5<725),b1:b5)) Regards, "pdgaustintexas" wrote: I need a formula that will add amounts in Column B if it falls within certain range in Column C. If Column C is greater than 725, then add amount in Column B (answer will be $47). If Column C is greater than 600, but less than 725, then add amount in Column B (answer will be $45), and so. Ex: A B C Joe $10 655 Jim $20 745 Tom $25 550 Sam $27 738 Amy $35 627 -- pdgaustintexas ------------------------------------------------------------------------ pdgaustintexas's Profile: http://www.excelforum.com/member.php...o&userid=30600 View this thread: http://www.excelforum.com/showthread...hreadid=503537 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding amounts that fall within a range
Best not to use resource grabbing array formulae unless absolutely
necessary. Sumif or sumproduct will work in this case. -- Don Guillett SalesAid Software "Ashish Mathur" wrote in message ... Hi, You may want to try the following array formula (Ctrl+Shift+Enter). =sum(if(c1:c5725,b1:b5)) =sum(if((c1:c5600)*(c1:c5<725),b1:b5)) Regards, "pdgaustintexas" wrote: I need a formula that will add amounts in Column B if it falls within certain range in Column C. If Column C is greater than 725, then add amount in Column B (answer will be $47). If Column C is greater than 600, but less than 725, then add amount in Column B (answer will be $45), and so. Ex: A B C Joe $10 655 Jim $20 745 Tom $25 550 Sam $27 738 Amy $35 627 -- pdgaustintexas ------------------------------------------------------------------------ pdgaustintexas's Profile: http://www.excelforum.com/member.php...o&userid=30600 View this thread: http://www.excelforum.com/showthread...hreadid=503537 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with using range names in sum function | Excel Worksheet Functions | |||
Array to named range conversion... | Excel Discussion (Misc queries) | |||
Adding Cell Range with Text | Excel Worksheet Functions | |||
Problem adding a range using Sumif function. | Excel Worksheet Functions | |||
Adding Range Names | Excel Worksheet Functions |