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.

✔ 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

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

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.

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.

You can't have a formula and a hard-coded value in a cell at the same time. You can have a formula placethe 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

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.

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

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

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History