Help please, SUM of Formulas combining SUMIFS with COUNTIF

David Mason

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.

Kelly Moore

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

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

Kelly Moore

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

Kelly Moore

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

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

Kelly Moore

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

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