Add to total if there is data in another field?

Hi all, first post so please excuse the long winded version that ensues! ...

I sell books and have a spreadsheet with the data I need, such as title, ISBN, cost price, sell price, P&P, profit, etc.

I have a column with the "potential profit" based on making a sale, then another column with "actual profit" which I populate when I actually make the sale. I originally totalled the column for "sell price" but this obviously gives me the total potential sale value of all the books, not the actual sale value and I could potentially create another column and populate this with the sale value when I sell a book like I do for potential profit and actual profit but would there be a way to have a running total of the sales column based on an entry in the "actual profit" column.

So, for example, I have my "sell price" column, my "potential profit" column and "actual profit" column. When I make a sale, I enter the amount I made in profit in the "actual profit" column and a formula recognised I've entered something in that column and then add's the "sell price" to a total at the top of the sheet. Another sale, another entry in another "actual profit" column and another addition to the sell total. All the data concerning the individual books are on one line of course, such as title A1, ISBN B1, Sell Price C1, Potential Profit D1, Actual Profit E1, etc.

Hope this all makes sense :) Mark

edit retag close merge delete

Sort by » oldest newest most voted

The function you're looking for is called SUMIF. In the example below, the formula for F2 is =SUMIF(E2:E5,">0",C2:C5).

title   ISBN        Sell Price  Potential Profit    Actual Profit   Sell Total
~~~~~~  ~~~~~~~~~   ~~~~~~~~~~  ~~~~~~~~~~~~~~~~    ~~~~~~~~~~~~~   ~~~~~~~~~~
Book A  123456789   $5.00$2.00                               $25.00 Book B 123456788$10.00      $3.00$2.50
Book C  123456787   $15.00$4.00               $3.50 Book D 123456786$20.00      \$5.00

more