Adding amounts that fall within a range

[pdgaustintexas]

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

[Sandy Mann]

For greater than 725 use:

=SUMIF(C1:C5,">725",B1:B5)

for greater than 600 and less then or equal to 725 try:

=SUMIF(C1:C5,"<=725",B1:B5)-SUMIF(C1:C5,"<600",B1:B5)


--
HTH

Sandy
[email protected]
[email protected] with @tiscali.co.uk


"pdgaustintexas"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Don Guillett]

try
=SUMIF(J:J,">"&725,K:K)
=SUMPRODUCT((J2:J22>725)*I2:I22)


=SUMPRODUCT((J2:J22>600)*(J2:J22<725)*I2:I22)
--
Don Guillett
SalesAid Software
[email protected]
"pdgaustintexas"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bernard Liengme]

Hi Texan:
> If Column C is greater than 725, then add amount in Column B (answer
> will be $47).
=SUMPRODUCT(--(C1:C100>725), B1;B100)
=SUMPRODUCT(--(C1:C100>D1), 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:C100>600),--(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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Ken Johnson]

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

[Ashish Mathur]

Hi,

You may want to try the following array formula (Ctrl+Shift+Enter).

=sum(if(c1:c5>725,b1:b5))
=sum(if((c1:c5>600)*(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
>
>

[Don Guillett]

Best not to use resource grabbing array formulae unless absolutely
necessary. Sumif or sumproduct will work in this case.

--
Don Guillett
SalesAid Software
[email protected]
"Ashish Mathur" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> You may want to try the following array formula (Ctrl+Shift+Enter).
>
> =sum(if(c1:c5>725,b1:b5))
> =sum(if((c1:c5>600)*(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
>>
>>