Formula Remediation
I asked the AI tool in Smartsheet to help create a formula that achieves the following (the name of the "Engineer" has been changed for discreteness):
i want to create formula that would give [Lead Allocation] * [WIP Revenue] when [Engineer Lead] is "Bob Smith" and otherwise give 0, but I want this formula to sum it up for the entire grid so i could put it in a summary field
Note: Revenue indicates $ amount and Allocation indicates %
I get the following formula, but it is an invalid operation:
=SUMIF([Engineer Lead]:[Engineer Lead], "Bob Smith", [Lead Allocation]:[Lead Allocation] * [WIP Revenue]:[WIP Revenue])
I ask Copilot to remediate it and it gives the following to no avail:
=SUM(COLLECT([Lead Allocation]:[Lead Allocation] * [WIP Revenue]:[WIP Revenue], [Engineer Lead]:[Engineer Lead], "Bob Smith"))
Please help!
Thanks, RD
Answers
-
Hi @Ronny Dorian,
Can you share a screenshot of the sheet? Hide all personal info..
I dont think you can do a calculation within a COUNTIF formula. You can try something like this:
=SUMIF([Engineer Lead]:[Engineer Lead], "Bob Smith",[WIP Revenue]:[WIP Revenue]) * SUMIF([Engineer Lead]:[Engineer Lead], "Bob Smith", [Lead Allocation]:[Lead Allocation])
It would be easier to help if I could see it.
Itai Perez
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
-
Hey Itai,
Thank you for your reply! This actually works now, however, that Lead Allocation column is a %, and what the formula is doing now is adding those up (95+100+20) and so on, taking the sum of that and multiplying with that revenue figure. What I am really looking for is a formula that will sum up an @row calculation for each of the rows where the conditions apply, without having to create an additional formula. I hope that makes sense.
Thanks,
Ronny
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!