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

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

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

Dan E

"Kim" wrote in message
...
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

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(B26,C2, ""), and in F2 put =IF(B26,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


"Kim" wrote in message
...
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

Kim

=SUMIF($B$2:$B$4,"6",C2:C4)
and
=SUMIF($B$2:$B$4,"6",D2:D4)

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

Regards

Trevor


"Kim" wrote in message
...
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

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!

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

-----Original Message-----
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(B26,C2, ""), and in F2 put =IF
(B26,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


"Kim" wrote in message
...
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


.

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

-----Original Message-----
Kim

=SUMIF($B$2:$B$4,"6",C2:C4)
and
=SUMIF($B$2:$B$4,"6",D2:D4)

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

Regards

Trevor


"Kim" wrote in message
...
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


.

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

-----Original Message-----
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!
.