I have the following data set:

Term Amount
0233 1,240
0233 3,655
0240 10
0240 151
0243 675


I'm trying set up a table to sum the total amount that meets the
following conditions:

1. Term = 0233
2. for amounts between 1,000 and 2,000

What formula is best for this operation and can anyone help me out with
how to set it up?

Brigitte


--
Brigitte
------------------------------------------------------------------------
Brigitte's Profile: http://www.excelforum.com/member.php...o&userid=32782
View this thread: http://www.excelforum.com/showthread...hreadid=548604

One way ..

Assuming data within A2:B10, col A = Term, col B = Amount

Placed in say, E1:
=SUMPRODUCT(($A$2:$A$10="0233")*($B$2:$B$101000)* ($B$2:$B$10<2000),$B$2:$B$10)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brigitte" wrote:

I have the following data set:

Term Amount
0233 1,240
0233 3,655
0240 10
0240 151
0243 675


I'm trying set up a table to sum the total amount that meets the
following conditions:

1. Term = 0233
2. for amounts between 1,000 and 2,000

What formula is best for this operation and can anyone help me out with
how to set it up?

Brigitte


--
Brigitte
------------------------------------------------------------------------
Brigitte's Profile: http://www.excelforum.com/member.php...o&userid=32782
View this thread: http://www.excelforum.com/showthread...hreadid=548604

I'm getting a #value. Do you know how to troubleshoot this type of
output?


--
Brigitte
------------------------------------------------------------------------
Brigitte's Profile: http://www.excelforum.com/member.php...o&userid=32782
View this thread: http://www.excelforum.com/showthread...hreadid=548604

"Brigitte" wrote:
I'm getting a #value. Do you know how to troubleshoot this type of
output?

The ranges: $A$2:$A$10, $B$2:$B$10 need to be identical in size
Re-check this in your adaptation of the formula to suit over there ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks. I can't believe I overlooked my ranges. I had them set for a
single cell rather than the full column.

One more thing....
I also need a count of the amounts that make up the total matching the
same criteria. Ideas?


--
Brigitte
------------------------------------------------------------------------
Brigitte's Profile: http://www.excelforum.com/member.php...o&userid=32782
View this thread: http://www.excelforum.com/showthread...hreadid=548604

=SUMPRODUCT(($A$2:$A$10="0233")*($B$2:$B$101000)* ($B$2:$B$10<2000))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Brigitte" wrote in
message ...

Thanks. I can't believe I overlooked my ranges. I had them set for a
single cell rather than the full column.

One more thing....
I also need a count of the amounts that make up the total matching the
same criteria. Ideas?


--
Brigitte
------------------------------------------------------------------------
Brigitte's Profile:
http://www.excelforum.com/member.php...o&userid=32782
View this thread: http://www.excelforum.com/showthread...hreadid=548604

"Brigitte" wrote:
Thanks. I can't believe I overlooked my ranges. I had them set for a
single cell rather than the full column.

Glad you got it working !

One more thing....
I also need a count of the amounts that make up the total matching the
same criteria. Ideas?

This slight adaptation of the earlier formula returns the count:
=SUMPRODUCT(($A$2:$A$10="0233")*($B$2:$B$101000)* ($B$2:$B$10<2000))

[the last term: .. ,$B$2:$B$10) in the earlier formula is removed]
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---