ExcelBanter - Adding amounts that fall within a range

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Adding amounts that fall within a range (https://www.excelbanter.com/excel-worksheet-functions/66519-adding-amounts-fall-within-range.html)

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

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

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

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

Adding amounts that fall within a range