Calculate Win/Loss on a quote log

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.

Find more posts tagged with

Formulas

Comments

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.

Sheet 1 Win Loss.PNG

Sheet 2 Totals.PNG

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

Sign in to join the conversation!

Quick Links

Trending discussions

Calculate number of days between two dates in the same column on the same sheet
Hello, Can a combination of the INDEX(COLLECT and NETDAYS Formula be used to get the number of days between two dates in the same column? I want to get the number of days between two dates on the same sheet, in the same column, that have matching names and different project types. For Example: Studio Name is equal to…
Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…