multiple conditions sum

B

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

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)
 
M

Max

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

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 ..
 
B

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?
 
B

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)
 
M

Max

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

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?
Click to expand...

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]
 
B

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)
 
M

Max

Bob Phillips said:
I thought you might be in the land of nod by now Max <g>
Click to expand...

Aha, but I was in serious training
to stay awake ahead of the World Cup <bg>
All the best to England in the event !
 
B

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)
 
Top