Assistance w/ automated Checkbox based on multiple conditions

edited 04/27/21 in Using Smartsheet
04/27/21 Edited 04/27/21
Accepted

Hi All,


I'm after some assistance regarding a formula to automate a checkbox based on multiple conditions. These are;

Column "Requested by" is populated with a date. Column "Site" is populated. Column "Site contact" is populated. Column "Quantity" is populated. Column "Product" contains the word "Device"

Then a sum is used to calculate the total number in column "Quantity" and confirm it is less than a certain figure. Currently I have as per below and believe the SUM area is the incorrect part. It may be an issue that the Quantity column also contains words as headings on top of values. Ideally if possible I want to sum only the values that correspond to the rows where the Product column displays "Device"

=IF(AND(ISDATE([Requested by]@row), ISTEXT([Site]@row), ISTEXT([Site contact]@row), ISTEXT([Quantity]@row), ([Product]@row) = “Device”, SUM(Quantity:Quantity)<1145), 1, 0)

Thanks,

Best Answer

  • Sam M.Sam M. ✭✭✭✭
    Accepted Answer

    Hi @Steven Watson,

    I did some testing:


    This is the formula I used in the column “Formula Checkbox”:

    =IF(AND(ISDATE([Requested by]@row), ISTEXT([email protected]), ISTEXT([Site contact]@row), NOT(ISBLANK([email protected])), [email protected] = "Device", SUMIF(Product:Product, "Device", Quantity:Quantity) < 1145), 1, 0)



    I took the SUM and added a SUMIF.

    SUMIF(Product:Product, "Device", Quantity:Quantity) < 1145


    It will check all the Product column if it has the word “device”, if it does, it will sum what it has in Quantity. In the first image above they are checked because the sum based on “device” is below 1145.


    If they surpass 1145 then the checkbox will uncheck.


    Hope it helps,

    Sam

Answers

  • Sam M.Sam M. ✭✭✭✭
    Accepted Answer

    Hi @Steven Watson,

    I did some testing:


    This is the formula I used in the column “Formula Checkbox”:

    =IF(AND(ISDATE([Requested by]@row), ISTEXT([email protected]), ISTEXT([Site contact]@row), NOT(ISBLANK([email protected])), [email protected] = "Device", SUMIF(Product:Product, "Device", Quantity:Quantity) < 1145), 1, 0)



    I took the SUM and added a SUMIF.

    SUMIF(Product:Product, "Device", Quantity:Quantity) < 1145


    It will check all the Product column if it has the word “device”, if it does, it will sum what it has in Quantity. In the first image above they are checked because the sum based on “device” is below 1145.


    If they surpass 1145 then the checkbox will uncheck.


    Hope it helps,

    Sam

  • Thanks Sam. Works great!

  • Sam M.Sam M. ✭✭✭✭

    No problem!

Sign In or Register to comment.