CountIFs Ignoring Blanks

Options

Good Morning,

I am trying to get this formula to work however I'm getting an Invalid Data type probably because in some instances there's a date but the duration hasn't been calculated yet. Essentially i would need it to ignore the cells that are blank in the duration column. Here's what I have:


=COUNTIFS([Date Received at Svc Center]:[Date Received at Svc Center], MONTH(@cell) = 12, (Duration:Duration), <=3)

Best Answer

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓
    Options

    Blanks don't matter to countifs. Is your date received at svc center column set to be date type? If so, do you have any data in that column that isn't a date? Running the month formula on text will pop an error, just wrap it in an iferror.


    =COUNTIFS([Date Received at Svc Center]:[Date Received at Svc Center], iferror(MONTH(@cell),0) = 12, Duration:Duration, <=3

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓
    Options

    Blanks don't matter to countifs. Is your date received at svc center column set to be date type? If so, do you have any data in that column that isn't a date? Running the month formula on text will pop an error, just wrap it in an iferror.


    =COUNTIFS([Date Received at Svc Center]:[Date Received at Svc Center], iferror(MONTH(@cell),0) = 12, Duration:Duration, <=3

  • MailynA
    Options

    Thank you very much for your help, wrapping that Month formula in the IFERROR did the trick!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!