Solved What Formula To Put Value In Col B for Each Yes In Col A?

October 1, 2016 at 20:05:39
Specs: Windows 7
If I have 2 columns A & B. Column A will only show "yes" and "no" and Column B could show any number. How do I write a formula that looks for the "yes" only in column A and then will give me the value of Column B beside the "yes".
Example: A5=Yes and B5=5. I need a formula that will seek out the yes in Column A and give me the value of the cell beside it in Column B.

See More: What Formula To Put Value In Col B for Each Yes In Col A?

Reply ↓  Report •


✔ Best Answer
October 2, 2016 at 16:52:50
If your original post had had all of the information that you included in your latest post, we'd have been done already.

You do not want to "report what the number is next to it in Column B", you want to SUM all of the values in Column B where the corresponding cell in Column A contains Yes.

=SUMIF(A:A,"Yes",B:B)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
October 1, 2016 at 20:47:40
First, a posting tip:

When posting in a Help forum such as this one, please try to use a Subject Line that tells us something about your question. If everyone used a generic Subject Line such as "I Need A Formula" we wouldn't be able to tell one question from another and the Archives would essentially be useless.

I have edited your subject to be more relevant to your question.

As for your question, I'm not sure how we are supposed to determine the value you want in Column B for each "Yes" in Column A. Your example shows B5 = 5. Is that because of the Row number 5? If so, then put this in B1 and drag it down:

=IF(A1="Yes",ROW(),"")

The ROW() function will return the Row number of whatever row it is in. To see this, enter =ROW() in any cell. There is also a COLUMN() function to return a column number.

If that is not what you are looking for, please explain how the value for Column B is determined.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
October 2, 2016 at 14:37:56
First of all, thank you for clearing up the posting. I appreciate all your assistance.

Let me try to give you a better example. If column A shows whether or not a person took the bus to work (yes or no answers) and Column B shows how many people were on that bus (example: 25 people). I am trying to figure out a formula that will go down Column A and find only the Yes answers and then tell me how many people were on that bus on Cloumn B as in 25 people. So if A3 = Yes and B3 beside it =25 it would bring in 25 into the cell that has the formula. I hope this helps. Please let me know.


Reply ↓  Report •

#3
October 2, 2016 at 14:47:40
You can't have a formula and a hard-coded value in a cell at the same time. You can have a formula place the value in the cell - sort of. What actually happens is the formula evaluates to that value.

i.e. Put this in B3:

=IF(A3="Yes",25,"")

That formula will evaluate to 25 when A3 contains Yes and evaluate to a blank cell at all other times

However, I'm confused by this statement:

"...find only the Yes answers and then tell me how many people were on that bus on Cloumn B as in 25 people"

How would Excel know that 25 people were on the bus?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
October 2, 2016 at 15:29:05
The formula would look down Column A to find cells containing Yes then it would report what the number is next to it in Column B. So if Cloumn A showed a Yes by A1 and A7 it would look at the values of B1 and B7 and pull the values of those cells into let's say C1 where the formula is being implemented. So if B1 and B7 had 21 and 23 guests on board C1 would show 44. I am not sure that Excel can do this function, but I am hoping.

Reply ↓  Report •

#5
October 2, 2016 at 16:52:50
✔ Best Answer
If your original post had had all of the information that you included in your latest post, we'd have been done already.

You do not want to "report what the number is next to it in Column B", you want to SUM all of the values in Column B where the corresponding cell in Column A contains Yes.

=SUMIF(A:A,"Yes",B:B)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#6
October 2, 2016 at 17:56:40
Thank you sir. Your help has been much appreciatetd. This did work for me.

Reply ↓  Report •


Ask Question