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.
Comments
-
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 -
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!