Formula Remediation

Ronny Dorian

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

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.

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