Hi, Can someone help me figure this out. I'm trying to

Hi can someone please help me figure this formula out? I'm trying to get the number of open tasks. The task is considered open if 1, the check box is blank and the Date Completed is open and if a task has not yet been assigned to someone.


=IF(OR(ISBLANK("Done"), ISBLANK([Assigned To]), [Date Completed])1) = "Checkbox is blank"),1)

Answers

  • Sorry Just realised I forgot to say please, so here goes Please!!!!!

  • Hi VAC, thank you for the formula. I really appreciate your help. I added the formula but it looks like something is a little off because it didn't take.

    What would the formula be if we said: Count the unchecked boxes in the Done column

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @mcbrides613

    It looks like you have two extra closing parentheses in here which would cause an error. The OR Function will need you to list out each possibility first, then you can close it off to say what to do if you find any of these possibilities.

    IF(OR(this, that, this), "True", "False")


    A "blank" checkbox is also represented as 0, not as "blank", so we'll want to use your checkbox column = 0 as one of the criteria.

    I personally find it easier to review when I use the same structure [Column] = "this", so you can search to see if a column is blank by saying [Column]@row = "" instead of using ISBLANK


    You also don't have any row references in this formula. I presume you're writing a formula down one column to be able to create a count later, is that correct?

    If so, try a structure like this:

    =IF(OR(Done@row = 0, [Assigned To]@row = "", [Date Completed]@row = ""), 1)

    This will return 1 in that specific one row IF the column called "Done" is not checked, the column called "Assigned To" is blank, and the column called "Date Completed" is also blank. You can turn it into a Column Formula.


    If you're looking to just create the overall COUNT without wanting to return something in each row, then the formula you're looking for is actually a COUNTIFS Function. In this instance you'll want to list an entire column as the range - [Column Name]:[Column Name] instead of the one cell in that row with [Column Name]@row

    Try:

    =COUNTIFS(Done:Done, 0, [Assigned To]:[Assigned To], "", [Date Completed][Date Completed], "")

    This will give you a total count.

    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee Admin

    To follow-up with this comment, if you're only looking to count Unchecked boxes in a column titled "Done", try this:

    =COUNTIFS(Done:Done, 0)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!