i need help with a formula

  • Thread starter Thread starter Kim
  • Start date Start date
K

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