Imported Formulas from Excel

Happy Wednesday! I have been going in circles trying to figure out where this formula is going unparseable. I researched it against other similar (working) formulas in smartsheet and the Formula errors guidance- but I am still missing it- hoping a fresh set of eyes can help me pick out the culprit. Thank you!

=SUMIF($[Strategic Priority]$2:$[Est. CapEx(Excludes Int Labor) (k)]$72, "=1", [Est. CapExInt Labor (k)]2:[Est. CapExInt Labor (k)]72)

Answers

  • Purnima Gore
    Purnima Gore ✭✭✭✭✭✭

    Hi

    If you are able to just add the columns here with the data (screenshot from excel would be good) so I can see what you were achieving in the excel sheet.

    Thanks

    Purnima

    Purnima Gore

    Cierr Limited

    Your Time is Important, you want to Stay on Track, We can help you use the Right Tools

    https://www.cierr.com

  • Cat Werbeck
    Cat Werbeck ✭✭✭✭
    edited 10/15/20

    Hi Purnima,

    Thank you for reaching out, it's just a sum formula really based on Summing three columns when a name is equal to excel formula: =SUMIF($I$4:$N$65,"=3",P4:P65) Excel snapshot below- it's not doing anything but summing the columns. I am trying to build out a Metrics sheet with as much slicing and dicing as possible- things were going great until introducing is an $Label@row to indicate when these two conditions are met, sum these three datasets. I know I have too many parens below, but I think I have tried to move just about every comma, ({ and }) that I could. It looks like the conditions are picking up correctly and those references are the same as other formulas on the sheet that are working with COUNTIFS, I am just missing something below and can't put my finger on it. Appreciate any feedback :)


    Screenshot of my metric sheet SUMIFS formula that is Unparseable

    =SUMIFS({EST CapEx ex Int Labor}, {IT Strategic Priority}$Label@row Russ$1, ({Est. CapExInt Labor}, {IT Strategic Priority}$Label@row Russ$1, ({Est. Expense}$Label@row Russ$1)))

    Excel Doc just had summary lines floating in in columns that sum up- not by any conditional factors.

    Row Names (and column N1, etc) labels


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Cat Werbeck

    Try this. (You can't have locked references combined with @row)

    I was going to suggest that you remove the $, but I'm confused by this part.

    $Label@row Russ$1

    What is the column name? (Label / Russ?)

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Cat Werbeck
    Cat Werbeck ✭✭✭✭
    edited 10/15/20

    Label is picking up KUIB.

    Use Case: I want 3 columns (the capex/expense fields) when the Label is KUIB (the @Label row) and IT PM = Russ. I am using a metric sheet to collect data based on the source sheet. Let me know if that clarifies. :)


    Thanks for the help!

  • Cat Werbeck
    Cat Werbeck ✭✭✭✭

    Any other suggestions?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Cat Werbeck

    I hope you're well and safe!

    Excuse the late response.

    Crazy times and I missed coming back to you.

    Have you solved it, or do you still need help?

    Happy New Year & Stay safe!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!