Count Rows that are Open and Closed based with another condition

Hello, I tried to do a search here, but could not find what I was looking for. I have a sheet with each row being opened and closed with a date range. I would like to count the rows that have an open date but no closed date, and also count rows that have the closed date populated with any date. I also need to add a condition of Division that has 4 different values. My thought was to count rows that have the Division populated and the Closed column was blank. Here was my best attempt

=COUNTIFS(Division:Division, "D16", Closed:Closed, 'ISBLANK'())

My second count would be the Division and the Closed has any date, but since I couldn't get the first one, I haven't got as far as a draft formula.

Any help appreciated

Best,

Randy

Find more posts tagged with

Formulas

Comments

Andrée Starå

Hi Randy,

Try something like this.

=COUNTIFS(Division:Division; "D16"; Closed:Closed; ISBLANK(@cell))

The same version but with the below changes for your and others convenience.

=COUNTIFS(Division:Division, "D16", Closed:Closed, ISBLANK(@cell))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Hope that helps!

Let me know if you need help with the other formulas!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Randy Van Winkle

The first one showed as #unparseable, but the second one worked great.

For the second formula, I need to count rows assigned to D16 with any value in the date column "Closed". All my ideas come up with #unparseable as well

Thanks again

Andrée Starå

Happy to help!

Try something like this.

=COUNTIFS(Division:Division, "D16", Closed:Closed, NOT(ISBLANK(@cell)))

Did it work?

Randy Van Winkle

It did work! Thanks again!

Andrée Starå

Excellent!

I'm always happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.
How can I set up recurring tasks in Smartsheet?
I need tasks that repeat (daily, weekly, monthly, yearly). Each should show a due date and a “Completed” checkbox. When I check it off, I’d like the task to log in an archive sheet with the completed date and then reset on the main sheet with a new due date and cleared checkbox. Has anyone found a simple way to do this?
Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…