Imported Formulas from Excel

10/14/20
Answered - Pending Review

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 GorePurnima 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


  • Cat WerbeckCat 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 [email protected] 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}[email protected] Russ$1, ({Est. CapExInt Labor}, {IT Strategic Priority}[email protected] Russ$1, ({Est. Expense}[email protected] 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.

    [email protected] 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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Cat WerbeckCat 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 WerbeckCat 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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.