CountIfs Dates with ISBLANK

Rigoberto Huizar

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)

Paul Newcome

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)

Paul Newcome

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)

Rigoberto Huizar

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 🙄