Calculate Win/Loss on a quote log

MRCNathan

Hello

 

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

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.

Regards,

Chris

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.  

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