multiple conditions sum
 

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:confused:


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

multiple conditions sum
 

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:confused:


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

multiple conditions sum
 

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

multiple conditions sum
 

"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
---

multiple conditions sum
 

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

multiple conditions sum
 

=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

multiple conditions sum
 

"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
---

multiple conditions sum
 

PERFECT!!

Thanks so much!


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

multiple conditions sum
 

"Brigitte" wrote:
PERFECT!!
Thanks so much!

You're welcome !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

multiple conditions sum
 

Thanks for the assist, Bob !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

multiple conditions sum
 

I thought you might be in the land of nod by now Max <g

--
HTH

Bob Phillips

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

"Max" wrote in message
...
Thanks for the assist, Bob !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

multiple conditions sum
 

"Bob Phillips" wrote:
I thought you might be in the land of nod by now Max <g

Aha, but I was in serious training
to stay awake ahead of the World Cup <bg
All the best to England in the event !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

multiple conditions sum
 

I am one of the few Englishmen who don't believe that England have a chance,
with or without Rooney. My bet is on Italia.

--
HTH

Bob Phillips

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

"Max" wrote in message
...
"Bob Phillips" wrote:
I thought you might be in the land of nod by now Max <g

Aha, but I was in serious training
to stay awake ahead of the World Cup <bg
All the best to England in the event !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

multiple conditions sum
 

Bob,

All the best to Italy, then <bg !
(but think your heart is still on England ..)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bob Phillips" wrote:
I am one of the few Englishmen
who don't believe that England have a chance,
with or without Rooney. My bet is on Italia.