multiple conditions sum

[Brigitte]

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

[Max]

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$10>1000)*($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
>
>

[Brigitte]

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

[Max]

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

[Brigitte]

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?

[Bob Phillips]

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


--
HTH

Bob Phillips

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

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

[Max]

"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$10>1000)*($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
---

[Brigitte]

PERFECT!!

Thanks so much!

[Max]

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

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

[Max]

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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the assist, Bob !
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

[Max]

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

[Bob Phillips]

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

[Max]

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.