i need help with a formula

[Kim]

I need a formula that will allow me to add amounts in two
separate columns, but only the amounts that have a certain
variable in another column. I will try to make an example
below.

Job Name Probability Bag Count Contract Amount
School 10 100 $100.00
Gym 5 500 $5000.00
Pool 7 200 $2000.00

OK, I need to add up the Bag count and the Contract
amounts at the bottom of each column, but only the ones
with a probability above 6. Is there a formula that can
be that specific?

Thanks in advance,
Kim

 

[Dan E]

Assuming Job Name is in A1 . . .
In C5 =SUMIF($B$2:$B$4,">6",C2:C4)
In D5 =SUMIF($B$2:$B$4,">6",D24)

B2:B4 is the range of the probabilities
C2:C4 & D24 is the range to be summed

Dan E

 

[Ed]

One way is to use "helper columns". It's not pretty in some ways, but it's
easy to do and easy to track if you've got errors.

Assuming your example columns are A, B, C, and D, starting with headers in
row 1, in E2 put =IF(B2>6,C2, ""), and in F2 put =IF(B2>6,D2, ""). Run
these down to the bottom of your range. At the botom of Column C, put
=SUM(E2:Exxx) (where xxx is the number of the last row). The bottom of
Column D would have =SUM (F2:Fxxx).

Hope this helps.
Ed

 

[Trevor Shuttleworth]

Kim

=SUMIF($B$2:$B$4,">6",C2:C4)
and
=SUMIF($B$2:$B$4,">6",D24)

assuming that the probability is in column B, and the bag count and contract
amounts are in columns C and D respectively.

Regards

Trevor

 

[Mark Bigelow]

Use an array formula:

Assuming Probabilities are in column B and Bags are in Column C and that
there are only 10 rows of data.

=SUM(IF('B2:B11' > 6, 'C2:C11', 0))

After you type that formula, hit Cntrl-Enter. Otherwise, it won't work.

Please let me know if you have any problems.

Mark

---
Mark Bigelow
mjbigelow at hotmail dot com
http://hm.imperialoiltx.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[kim]

Thanks so much, this is exactly what i needed to do.

 

[kim]

Thanks so much, this is exactly what i needed to do.

 

[kim]

Thanks so much, this is exactly what i needed to do.