CountIfs Dates with ISBLANK
I am having trouble with my formula. A portion works but as soon as I insert the IF and ISBLANK Statement it Fails. I want the formula to use the "Actual Plan Actual Finish" column and if that is empty then use the "Action Plan Finish Date". I'm not sure where to insert that piece of the formula. Any help would be appreciated.
=COUNTIFS({Action Plan Request Date}, IFERROR(WEEKNUMBER(@cell), 0) <= [1]1, {Action Plan Actual Finish}, IFERROR(WEEKNUMBER(@cell), 0) >= [1]1, {Action Plan Finish Date}, IFERROR(WEEKNUMBER(@cell), 0) >= [1]1, {Action Plan Actual Finish}, IFERROR(WEEKNUMBER(@cell), 0) >= [1]1)
Best Answer
-
You would need to add two separate COUNTIFS together to accomplish this. The second would include the criteria of {Action Plan Actual Finish} being blank.
=COUNTIFS(Action Plan Request Date}, IFERROR(WEEKNUMBER(@cell), 0) <= [1]1, {Action Plan Actual Finish}, IFERROR(WEEKNUMBER(@cell), 0) >= [1]1) + COUNTIFS({Action Plan Request Date}, IFERROR(WEEKNUMBER(@cell), 0) <= [1]1, {Action Plan Actual Finish}, @cell = "", {Action Plan Finish Date}, IFERROR(WEEKNUMBER(@cell), 0) >= [1]1)
Answers
-
You would need to add two separate COUNTIFS together to accomplish this. The second would include the criteria of {Action Plan Actual Finish} being blank.
=COUNTIFS(Action Plan Request Date}, IFERROR(WEEKNUMBER(@cell), 0) <= [1]1, {Action Plan Actual Finish}, IFERROR(WEEKNUMBER(@cell), 0) >= [1]1) + COUNTIFS({Action Plan Request Date}, IFERROR(WEEKNUMBER(@cell), 0) <= [1]1, {Action Plan Actual Finish}, @cell = "", {Action Plan Finish Date}, IFERROR(WEEKNUMBER(@cell), 0) >= [1]1)
-
Thank you Paul. Much appreciated. That provides me a number. I'm a newb to smartsheet so I appreciate your patients. I'm thrown off because in essence shouldn't the formula you provided me given me the same results as the formula below? But I got two different results.
=COUNTIFS({Action Plan Week Requested Date}, <=[1]1, {Action Plan Week Finish}, >=[1]1)
The Action Plan Week Request Column has a Number in it with a WeekNumber formula and Action Plan Week Finish has the same thing. (This is used in the above formula).
Essentially I was condensing everything into one formula so that way I do not produce extra columns on the smartsheet.
=COUNTIFS(Action Plan Request Date}, IFERROR(WEEKNUMBER(@cell), 0) <= [1]1, {Action Plan Actual Finish}, IFERROR(WEEKNUMBER(@cell), 0) >= [1]1) + COUNTIFS({Action Plan Request Date}, IFERROR(WEEKNUMBER(@cell), 0) <= [1]1, {Action Plan Actual Finish}, @cell = "", {Action Plan Finish Date}, IFERROR(WEEKNUMBER(@cell), 0) >= [1]1)
I have terrible logic, Sorry 🙄
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!