Help please, SUM of Formulas combining SUMIFS with COUNTIF

Options
David Mason
David Mason ✭✭
edited 02/24/22 in Formulas and Functions

Hi Experts


I hope you can help me, I am a newbie to formulas and need to build a solution to help me with my resource estimations.

I have built these formulas individually and they appear to work. However when I combine them I get error #UNPARSEABLE

=SUM((SUMIFS({Resource Estimation Calcs Range 5}, {Resource Estimation Calcs Range 2}, HAS(@cell, [PM Estimate]1), {Resource Estimation Calcs Range 3}, HAS(@cell, "All")), /(COUNTIF(Stage:Stage, Stage@row)))


this is driving me nuts :(, I really hope someone can spot what I am doing wrong.

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @David Mason

    Have you tried the formula without the initial SUM?

    =SUMIFS({Resource Estimation Calcs Range 5}, {Resource Estimation Calcs Range 2}, HAS(@cell, [PM Estimate]1), {Resource Estimation Calcs Range 3}, HAS(@cell, "All"))/COUNTIF(Stage:Stage, Stage@row)

    As a good practice know that you can rename the Smartsheet generically named cross sheet ranges to reflect the actual column names in the sheet. This will help you and the community troubleshoot formulas.

    Does the formula work for you?

    Kelly

  • David Mason
    David Mason ✭✭
    Answer ✓
    Options

    @Kelly Moore Thank you so much, That has worked perfectly and really saved my sanity 😁👍️

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Glad it worked!

    I wondered if your ranges were multi-select dropdowns that forced you to use HAS. If you are looking in a regular column just for "All", you don't need HAS to do so.

    So happy it's all working for you

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @David Mason

    Have you tried the formula without the initial SUM?

    =SUMIFS({Resource Estimation Calcs Range 5}, {Resource Estimation Calcs Range 2}, HAS(@cell, [PM Estimate]1), {Resource Estimation Calcs Range 3}, HAS(@cell, "All"))/COUNTIF(Stage:Stage, Stage@row)

    As a good practice know that you can rename the Smartsheet generically named cross sheet ranges to reflect the actual column names in the sheet. This will help you and the community troubleshoot formulas.

    Does the formula work for you?

    Kelly

  • David Mason
    David Mason ✭✭
    Answer ✓
    Options

    @Kelly Moore Thank you so much, That has worked perfectly and really saved my sanity 😁👍️

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Glad it worked!

    I wondered if your ranges were multi-select dropdowns that forced you to use HAS. If you are looking in a regular column just for "All", you don't need HAS to do so.

    So happy it's all working for you

    Kelly

  • David Mason
    Options

    They are multi-select dropdowns - thank you for the support. All is working excellent

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!