Question

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 · Accepted Answer

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 · Accepted Answer

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

Kelly Moore · Accepted Answer

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