Formula Remediation

Ronny Dorian
Ronny Dorian ✭✭✭✭
edited 11/18/24 in Formulas and Functions

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

Tags:

Answers

  • Itai Perez
    Itai Perez ✭✭✭✭✭✭

    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/

  • Ronny Dorian
    Ronny Dorian ✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!