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
Comments
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
Happy to help!
Try something like this.
=COUNTIFS(Division:Division, "D16", Closed:Closed, NOT(ISBLANK(@cell)))
Did it work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
It did work! Thanks again!
-
Excellent!
I'm always happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!