Ask Your Question

Add to total if there is data in another field?

asked 2017-09-28 18:14:06 +0200

marko2002 gravatar image

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-09-28 18:44:34 +0200

Jim K gravatar image

updated 2017-09-28 18:45:01 +0200

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
edit flag offensive delete link more


You're a star, thank you for that one, works a treat and thanks for the detail on the sheet too, helped a million :) Mark

marko2002 gravatar imagemarko2002 ( 2017-09-28 19:02:02 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-09-28 18:14:06 +0200

Seen: 33 times

Last updated: Sep 28 '17