Sum of one column, but the numbers being calculated could change based on another column

[ciresuark]

I have an issue trying to calculate the sum of A1:A22. Here is information regarding what I am trying to do:

A1:A22 are numbers ranging from 1-10.
B1:B22 are numbers ranging from 0-200.

If a number in B1:B22 is greater than or equal to 120 the number in the correlating A cell is divided by 2 unless the number in the A cell is 1 then 1 would be subtracted.

Example:

A1 is 7, B1 is 122. Sum = 3.5
A2 is 1, B2 is 120. Sum = 0
A3 is 4, B3 is 14. Sum = 4
Total 7.5 round up to 8.

I hope this make sense.

The formula is being entered in Cell C1

[Pete_UK]

Try this in C1:

=IF(B1>120,IF(A1=1,0,A1/2),A1)

You didn't specify what is to happen if B is less than or equal to 120, but your third example seems to suggest that you just want the value of A.

Hope this helps.

Pete

[ciresuark]

You are correct in your assumption. For this to check cells B1:B22, I would repeat the formula for each one correct? Or would you recommend a cheater column to add them up instead of a long If formula?

[Pete_UK]

Because of the way you had laid out the examples, I assumed you would copy the formula down to C22, and then use a simple SUM formula to get the overall result:

=SUM(C1:C22)

You could get the result in a single array* formula, like this:

=SUM(IF(B1:B22>120,IF(A1:A22=1,0,A1:A22/2),A1:A22))

and as this is an array* formula you need to commit it using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual < Enter >.

Hope this helps.

Pete