Is there a way in Excel to...?

Question

Ok, I am making a spreadsheet to show my profit in selling on ebay. I have coloums that have; selling price, auction cost (ebays take), and sold (yes or no) then I have selling price - cost = profit. BUT is there a way to only total the profit if i have yes in the sold coloum? Right now it is adding all my auctions even if they are not sold. Please help and thanks for your help in advance.
Kyle

Answer 1

Say Price is Column A
Cost is Column B
Yes/No is column C
Profit is Column D


Use the following formula on the profit column

=If(C1="Yes",A1-B1,0)

This will put the profit on column D only if column C is "Yes". Else it puts a zero. This way you have a column with only the profits, which you can add up. I am assuming row 1 on this example

Also, please make sure that "Yes" in your formula is spelled EXACTLY the way you spell it on your "Yes" or "No" column.
********************
One neat trick to avoid common spelling mistakes is to create an input list.

First, create a list off the main area of the spreadsheet with the words "Yes" and "No". Make sure they are spelled exactly the way you spell it in your formula for column D.

Then, select the cells on your Yes/No column.

Go to the pull-down menu at the top select DATA > VALIDATION.

Select the "List" option from the validation criteria and then use the source option to select the cells showing your Yes and No list you created above.

What you are doing is limiting the input options on your Yes/No column to EXACTLY the options on your list. This way the yes/no column will only accept values that are spelled the way you want.

Hope this helps.

Answer 2

you can 1st go to data>filter>auto filter. then u can click on the little white tab which appears at the top of the columns and filter your sold column for yes. and finally you can punch in your formula to sum for these rows.

Hope this was helpful.

Answer 3

Use this formula assuming your columns are A, B, C, etc

Put this in cell D1:

=if(C1="Yes",A1-B1,0)

drag this down the rest of the rows and it will update the formula correctly for all other rows.

Answer 4

yes, you can used the If function to only add the profit from those which are sold.