Calculate Win/Loss on a quote log

edited 12/09/19 in Formulas and Functions



I have a quote log that has a win/loss column. I want to populate a cell that takes the $ amount of the won bids, adds them up to show a total $ amount of won bids. Same for lost bids. 

I also want this result to auto update if the win/loss value is changed. 

Having trouble with the formula to make this work. Any help is appreciated. 



  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    Hi MRCNathan,

    If I'm reading that right, you have an Amount column and a Win/Loss column and you're looking to place 2 formulas in 2 different cells on your sheet to:

    • SUM all the Amount values with a corresponding Win value in the Win/Loss column
    • SUM all the Amount values with a corresponding Loss value in the Win/Loss column
    • Update both cells if Amount or Win/Loss is changed

    If so, place these formulas in different cells and you're good to go:

    • =SUM(COLLECT(Amount:Amount, [Win/Loss]:[Win/Loss], "Win"))
    • =SUM(COLLECT(Amount:Amount, [Win/Loss]:[Win/Loss], "Loss"))

    Just make sure that you don't put them in either the Amount or Win/Loss columns and it will continue calculating as you sheet grows (i.e. as rows are added).

    Hope this helps.



  • MRCNathan

    Chris thank you for the reply and I think you are on the right track. 

    I have attached some screenshots to help. What I'm looking for is: 

    If the value is "Win" in the Win/Loss/Cancelled column on Sheet 1 then take the $ amount next to it (In the Amount $ column) and send it to a second sheet, and on the second sheet total the amounts. 

    I ultimately trying to get total wins and total losses as well as a total of all quote activity so I can pull reports to my dashboard for my management team. 


    Thanks again for your help.  

    Sheet 1 Win Loss.PNG

    Sheet 2 Totals.PNG

  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    Hi Nathan,

    If you're ultimately looking to create Reports and Dashboards for Management, could you not follow the path I suggested?

    Simply create a Report that displays the 2 rows I provided formulas for (see below). You could then create a 3rd row at the top to SUM(Amount:Amount).

    Trying to drag figures into another sheet via cell-link or cross sheet reference seems overly complex.

    Kind regards,

    Chris McKay

    Down Under Smartsheet Support

    Screen Shot 2018-04-14 at 12.33.16 am.png

    Screen Shot 2018-04-14 at 12.35.29 am.png

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!