# Calculate Win/Loss on a quote log

Options
edited 12/09/19

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.

Tags:

• ✭✭✭✭✭✭
Options

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

• Options

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.

• ✭✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!