Heading need general formula for addition of 2 columns based on conditions?

Question

I have 3 Columns. Column A, Column B, Column C. Column A contains conditions and Column B contains some value and Column C contains some values.
Col A Col B Col C

  y            2    
  y            3  
  d            4  
  y            5             2   
  y            6             3     
  n                          7  
  b                          8  
  y                          9

Sum=

I need a Formula that sums values of B when there is no value in Column C and sum all values of Col C when there is no value in Column B but When there is value in both Column B and Column C then it prefer Column C and put its value in total sum and I want to add only values which have status y in Column A.

Example : like Column B has value = 5 and Column C has Value = 2 so my Formula should prefer 2 and add 2 in total sum and leave 5 means value of Column B . Else is simple means If I have Value in A just add those values in total Sum and If I have Value in B just add that Value in Total Sum.

I have a formula but it only works for adding values from both Columns. =SUMPRODUCT(--($C2:C9="y"); $B2:B9+$C2:C9) this is formula which adds value from all Columns that I have but I need a condition when Column B and C both have values and I need to prefer the value of Column C and choose only C value for total Sum.

Formula to prefer /choose 1 Column value from 2 columns based on conditions (For Addition)

I more simple way

Just Add all values for y from Col B and Col C but when We have values in both Col B and Col C then Prefer Column C and just consider value of Column C for total sum and ignore value of Col B

Axel Richter Axel Richter · Accepted Answer · 2017-01-11T07:20:30

I would have the logic like this:

Sum if "y" in A, then if C is empty, then B, else C.

{=SUM(IF($A$2:$A$10="y",IF($C$2:$C$10="",$B$2:$B$10,$C$2:$C$10)))}

This is an array formula. Input it into the cell without the curly brackets and press [Ctrl]+[Shift]+[Enter] to confirm. The curly brackets will then appear automatically.

Heading need general formula for addition of 2 columns based on conditions?

1 Answers