#BOOLEAN Expected Duplicate Month checkbox formula assistance

Formula I am using =COUNTIFS({F & A Monthly Updates Range 2}, Department@row, {F & A Monthly Updates Range 3}, "3").

The problem I am facing is when submissions are made multiple times for a duplicative month. For example, the form has a system column to capture the date the submission on the sheet form was made. If I submit June 1 then June 30 for my July submission then it will capture June twice and a #BOOLEAN Expected error appears on my tracker (snapshot). The tracker shown is referencing another sheet by department then month so it has a multiple criteria to look up.


Tags:

Best Answer

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    Hi, @Linda F

    Modify your current formula in the following manner:

    IF( YOUR_COUNTIF_EXPRESSION = 0, 0, 1)

    The checkbox column type requires a Boolean value: "TRUE or FALSE" or "1 or 0". Your original formula returns a value that is neither "1" nor "0" when multiple reports are submitted within the month. The modified formula will return FALSE(0) when the count is 0; otherwise, it will return TRUE(1) when the count is anything other than 0.

    =IF( COUNTIFS({F & A Monthly Updates Range 2}, Department@row, {F & A Monthly Updates Range 3}, "3") = 0, 0, 1)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!