Forum Discussion

bramleyas's avatar
bramleyas
Copper Contributor
Feb 06, 2025

Checkboxes not automatically updating using COUNTIF

Hey, all,

I'm using the following function to set the checkbox boolean of a cell based on the amount of other cells checked:

In cell A1: =IF(COUNTIF(A2:A3=2,TRUE),TRUE,FALSE)

The formula works, but only when I manually click in and out of the A1 cell. Is there a way to have the cell automatically update like it would in Google Sheets? Using Excel for web build 16.0.18602.42306 here. Appreciate the help!

excel
Excel for web
Formulas and Functions
office 365

5 Replies

  • smmartin1992's avatar
    smmartin1992
    Copper Contributor
    Feb 10, 2025

    I have the same issue that just started happening. I am using formulas that apply things when TRUE is in the cell, so the checkboxes have worked good so far. If I TYPE in the cells TRUE or FALSE it will work as normal. I can even check the box, then go to the cell with the formula, double click to open up the cell for editing, click the green check mark, and it will refresh it. I’m about to submit a bug report to Microsoft.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Feb 07, 2025

    Maybe I'm not seeing the bigger picture, but why not simplify the formula to be:

    =AND(A2,A3)

     

    • m_tarler's avatar
      m_tarler
      Bronze Contributor
      Feb 07, 2025

      I was assuming they want the IF and =2 for specific reasons but that really isn't the problem.  The problem here is that online version of Excel is not behaving with respect to these checkboxes.  Here are 2 checkboxes and 2 formulas looking them.  In 1 is the simple formula and in the other I added a volatile function.  Notice how after I clicked the 2 boxes the original still says FALSE but the other correctly updated to TRUE

      again, this is happening on the online version NOT the desktop version

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Feb 07, 2025

    I assume the formula you meant to use is:

    =IF(COUNTIF(B2:B3,TRUE)=2,TRUE,FALSE)

    and there does seem to be something buggy in the online version.  It works fine in the desktop version but online I can literally see it toggle and then revert back to the incorrect answer.  

    Although not great, by adding OFFSET() (which is a 'volatile function') it does seem to "fix" the problem:

    =IF(COUNTIF(OFFSET(B2:B3,0,0),TRUE)=2,TRUE,FALSE)

    You should also report the bug to MS and hopefully they can figure it out and fix it.

     

    Intersestingly enough this gives me an opportunity to investigate Volatile functions.  For example any of the following also work (i.e. are volatile) as expected:

    =IF(AND(B2,B3)+0*RAND(),TRUE,FALSE)

    =IF(AND(B2,B3)+0*NOW(),TRUE,FALSE)

    =IF(AND(B2,B3)+0*TODAY(),TRUE,FALSE)

    =IF(AND(B2,B3)+0*RAND(),TRUE,FALSE)

    and using INDEX() in the following case does not act as volatile:

    =IF(AND(INDEX(B2:B3,0)),TRUE,FALSE)

    but using INDEX() to help define a range in the following DOES act volatile:

    =IF(AND(B2:INDEX(B3,1))+0,TRUE,FALSE)

    Similar is true for TAKE()

    =IF(AND(TAKE(B2:B6,2)),TRUE,FALSE)   <= not volatile

    =IF(AND(B2:TAKE(B3,1)),TRUE,FALSE)    <= volatile

Resources