Number of Open Task

mcbrides613

What formula should I use to get the total number of OPEN task. I know it's open if 1. the box is blank, task has not been assigned or date has not been entered under Date Completed.

Kelly Moore

Hey @mcbrides613

The easiest way to do this is first add a helper column (a checkbox column will do) to keep track of these different conditions

In the checkbox helper column:

=IF(OR(ISBLANK([whatever column names goes with empty box]@row), ISBLANK([Assigned To]@row), [Date Completed]@rows=""),1)

*After adding the helper column and copying this formula into it, make sure to edit the formula so that the column names in the formula to match your actual column names.

Then you can get to the formula you wanted

=COUNTIFS([Helper column]:[Helper column], 1)

*Again, change the column name in the formula to match your actual column name.

Will this do what you need?

Kelly

Kelly Moore

Hey @mcbrides613

The easiest way to do this is first add a helper column (a checkbox column will do) to keep track of these different conditions

In the checkbox helper column:

=IF(OR(ISBLANK([whatever column names goes with empty box]@row), ISBLANK([Assigned To]@row), [Date Completed]@rows=""),1)

*After adding the helper column and copying this formula into it, make sure to edit the formula so that the column names in the formula to match your actual column names.

Then you can get to the formula you wanted

=COUNTIFS([Helper column]:[Helper column], 1)

*Again, change the column name in the formula to match your actual column name.

Will this do what you need?

Kelly

mcbrides613

Thank you Kelly for your support, I appreciate your assistance.

Kelly Moore

Hey

I saw your other question. Are you not able to get this formula to work? If you can provide a screenshot I would be happy to help. I think I may have misinterpreted by what you said by 'empty box'. Try deleting that whole term from my OR statement to see if that gives you what you want.

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

If interested, the information below could help with the syntax of your OR statement.

https://help.smartsheet.com/function/or

Kelly

mcbrides613

Thank you so much!

mcbrides613

Kelly Moore

Hey,

I am unclear with your DONE column. I can readily help you write the formulas but I'm trying to understand your data.

Please describe how the DONE column gets a checkmark in it. Do you do this manually? If manually, is this based on whether both the (1) task is assigned AND the (2)completed date has a date in it, or is it based on something completely different?

I ask because I believe the Done column is the 'empty box' you referred to in your first post. What I'm trying to clarify is if actually

DONE = Task Assigned not blank AND Completed Date not blank

Then, the calculation for Open = Total Tasks in list - Done Tasks

If Both my "Done" and "Open" are correct interpretations, then only a COUNTIFS formula is needed. If DONE is being checked with different criteria than I summarized, then you may need the helper column I originally described. I will wait for your clarification.

Kelly

mcbrides613

Hi Kelly, I'm do understand the helper column. I need more clarity on that.

Yes, you are correct with you understanding of the Done column:

if DONE the box will be checked. Which means the box is checked and the Date Completed is filled in. Which means the task is no longer open.

if the DONE check box is blank that means the task is Open. The task is Open when the box isn't checked, or the Assigned To is blank and/or the Date Completed is blank.

I hope this helps and you have a formula. Thank you so much, I really appreciate your help.

mcbrides613

Hi Kelly, I think I am making this more complicated then I have to. I think I just need a simple formula.

total number of boxes and/or cells unchecked? can you help me with this?

Kelly Moore

Hey

Good question on what is a helper column. It is a column that we add to a sheet to help smartsheet do an operation that we cannot do otherwise. Often we can shove these helper columns to the far right and hide them once they are built.

Anytime you are making decisions across a row and then counting those decisions for the whole sheet, you need a column to first collect the decisions. In your case, you're already doing this with your Done Once I could see the Done column in your sheet I was verifying that DONE was collecting the needed information so that, yes, all we need to do is count the DONE's. That is the simplest approach.

We do need a place to collect the total count information for all the Open tasks. It will always be just a single number that summarizes Open Tasks for the entire sheet. One approach is to use the SUMMARY FIELDs found on the right rail of all your sheets. Summary fields can be used in formulas within the same sheet, they can be used in reports, as well as in dashboards. Without knowing exactly how you wish to use this data, Summary field will be my recommended approach.

When you go into the Summary fields, if you haven't used it before, at the bottom of the screen will be a button to Create a Summary Field. Do this. Then you will get a dialogue box that resembles the dialogue when you are creating a new column. We will leave the default to a Text/Number Field. You can call this field whatever you like. I'll call it Open Tasks

=COUNTIFS(Tasks:Tasks, <>"", Done:Done, 0)

This says to count all of your Tasks where the Task field is not empty and where Done is not checked. These two conditions have to apply on the same row to be counted.

Does this work for you? If you have any questions, come back and ask - it's always important to ask questions until you fully understand, or until the formula does exactly what you need it to do.

Kelly