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

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.

In 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.

I see you've got something of a formula here, but the manner in which you are asking (and your comment response to Gary's Student) suggests to me that you are asking someone to do the bulk of the work. That's not really how Stack Overflow operates - we would rather you do the design and initial attempt, and ask a question on how to fix what you have. — halfer
@halfer I worked for it and try to figure out the solution as I had a lot of data so i just tried to explain it by example. I am not asking him to do the job. What i asked from him was actually my problem because i wanted to solve a large set of data . I think u have written some wrong stuff that does not look nice . Sorry from my side but i have to say this that u have used some harsh words. This is help forum sometimes there is a little thing that is easier for u but difficult for others. Anyway thanks for ur suggestion . I will take care in future. — user7241892
My words are not intended harshly, I can assure you. It really is worth noting that we get requests for free work many times every single day, and unless you strongly differentiate your questions from such requests, your question will be treated the same as them. It is not a question of whether it is easy for me at all - I don't use Excel :-). — halfer
@Halfer .I know this but I cannot use some pure technical words here to make my question very intellectual. As we have to explain people in words so that they are able to understand what i want to ask so the only way is to be as simple as possible so it does not mean that we are just writing questions without doing some efforts but it was my understanding . I thanks you again and keep helping people. This is nice forum and sometime please give people some space. Takecare. — user7241892

Gary's Student Gary's Student · Accepted Answer · 2017-01-10T16:52:43

In D1 enter:

=IF(A1<>"y","",IF(C1="",B1,C1))

and copy down. Then in another cell enter:

=SUM(D:D)

use:

=IF(A1<>"y";"";IF(C1="";B1;C1))

if your settings require it.

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

1 Answers