Assistance w/ automated Checkbox based on multiple conditions

Steven Watson

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,

Sam M.

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(Site@row), ISTEXT([Site contact]@row), NOT(ISBLANK(Quantity@row)), Product@row = "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

Sam M.

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(Site@row), ISTEXT([Site contact]@row), NOT(ISBLANK(Quantity@row)), Product@row = "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

Steven Watson

Thanks Sam. Works great!

Sam M.

No problem!