Help with Formula!

lauratmc

Hello,

I'm pretty new to smartsheet but slowly working out how to create some functioning formulas! I wonder if anybody might be able to help me with the latest one please?

I have multiple columns containing different data types (some are tick boxes, others are dates and some will say 'N/A' - I am trying to write a formula to return 'Package Complete' is all columns are not blank, or return 'Documents Pending' is any of them are blank...

Is this possible??

Thanks!

Laura

Andrée Starå

Hi Laura,

Welcome to the Community and the wonderful world of Smartsheet!

Yes, it's possible!

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

We can start by finding out exactly how many cells are in the range.

 

=COUNTIFS([First Column]@row:Last Column]@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))

 

Then we count how many are not blank.

 

=COUNTIFS([First Column]@row:Last Column]@row, NOT(ISBLANK(@cell)))

 

If we compare the two in an IF statement to generate "Package Complete" if they are the same number, otherwise generate "Documents Pending", then we get something like this...

 

=IF(first_formula = second_formula, "Package Complete", "Documents Pending")

 

=IF(COUNTIFS([First Column]@row:Last Column]@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))= COUNTIFS([First Column]@row:Last Column]@row, NOT(ISBLANK(@cell))), "Package Complete", "Documents Pending")