Count Rows that are Open and Closed based with another condition

Randy Van Winkle

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

 

 

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!