Countif with IsDate & IsBlank

Options

For my summary sheet:

I'm trying to count rows where there is a date selected (the assigned to OSU row) but no region selected (Region row).

I tried this but it did not work:

=COUNTIF(ISDATE([Assigned To OSU]@row), "cell contains a date",(ISBLANK([Region]@row))

In another summary row, I then want it to count the number of flags raised.


Best Answers

  • Lauren Kleitz
    Lauren Kleitz ✭✭✭✭
    Answer ✓
    Options

    Hello Tabitha!

    I think you will need to add a helper column to get the count you want!

    Here is how I would approach it:

    1. Add a column called something like "Assigned but No Region". I would make this a checkbox column with a column formula set up as =IF(AND(ISDATE([Assigned To OSU]@row), ISBLANK(Region@row)), true, false)
    2. Run your count off of that new column using this formula =COUNTIF([Assigned but No Region]:[Assigned but No Region], true)

    To count the flags raised in a symbol column using the flag symbol use this formula: =COUNTIF([Raise Flag]:[Raise Flag], 1)

    Let me know if that helps!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓
    Options

    I see. I think I mixed up your requirements there. I had the is date and Isblank requirements flip-flopped. Here is the correct version.

    =COUNTIFS(Region:Region, ISBLANK(@cell), [Assigned to OSU]:[Assigned to OSU], ISDATE(@cell))

Answers

  • Lauren Kleitz
    Lauren Kleitz ✭✭✭✭
    Answer ✓
    Options

    Hello Tabitha!

    I think you will need to add a helper column to get the count you want!

    Here is how I would approach it:

    1. Add a column called something like "Assigned but No Region". I would make this a checkbox column with a column formula set up as =IF(AND(ISDATE([Assigned To OSU]@row), ISBLANK(Region@row)), true, false)
    2. Run your count off of that new column using this formula =COUNTIF([Assigned but No Region]:[Assigned but No Region], true)

    To count the flags raised in a symbol column using the flag symbol use this formula: =COUNTIF([Raise Flag]:[Raise Flag], 1)

    Let me know if that helps!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Actually, I believe this is possible without a helper column. Try this:

    =Countifs(Region:Region, Isdate(@cell), [Assigned to OSU]:[Assigned to OSU], Isblank(@cell))

    Did that work?

  • Lauren Kleitz
    Lauren Kleitz ✭✭✭✭
    Options

    Mike's solution is definitely more elegant! I forgot about @cell there for a minute. 😊

  • Tabitha W.
    Tabitha W. ✭✭✭✭
    edited 12/11/20
    Options

    Thanks for the help from both of you. It's really appreciated!

    Lauren, I was able to get yours to report out correctly.


    Mike, your's is not unparsable, but it also isn't returning my test record.



    See below


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓
    Options

    I see. I think I mixed up your requirements there. I had the is date and Isblank requirements flip-flopped. Here is the correct version.

    =COUNTIFS(Region:Region, ISBLANK(@cell), [Assigned to OSU]:[Assigned to OSU], ISDATE(@cell))

  • Tabitha W.
    Tabitha W. ✭✭✭✭
    Options

    It works! Thanks again, you rock!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Awesome. Glad I could help you out! :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!