looking for a blank = how do I enter this into my formula?

Barbara Witt

I have a countif to look for "tbd", but I also want it to look if someone did not enter "tbd", but left the field blank.

I tried to use countifs and add " " after my "tbd", but it does not work. Suggestions?

Thanks so much for your assistance.

Kelly Moore

Hey @Barbara Witt

Try this

=COUNTIFS([your tbd column]:[your tbd column],OR(@cell = "tbd", @cell = ""))

Instead of @cell="" you can also use ISBLANK(@cell)

cheers,

Barbara Witt

Thanks, Kelly. That is what I am looking for. Is there something like ISNOTBLANK or HASAVALUE?

For another scenario I want to count how many project numbers I have if the date field is not empty (or if the date field has a date).

=COUNTIFS([your tbd column]:[your tbd column, ISDATE({DAX Ambient Inventory Range 2}))

=COUNTIFS([your tbd column]:[your tbd column, ISNOTDATE({DAX Ambient Inventory Range 2}))

The above does not work. How could this work?

I very much appreciate your continued support, Barbara

Kelly Moore

Hey Barbara

Is your COUNTIFS formula going on the same sheet or a cross referenced sheet? I ask because you have mixed the syntax of the ranges between same sheet and cross referenced sheets

When on the same sheet, we reference columns using [column name]:[column name].

For retrieving data from a different sheet, a cross-sheet referenced is created and it will be shown as {range} (As a good practice, edit the generic smartsheet range name to be sheet name -column name)

You're right that ISDATE will weed out both blanks and text (eg, 'tbd') in a date field.

COUNTIFS have the syntax of (range1, criteria1, range2, criteria2, range3, criteria3, etc). You always have to put in the range, criteria pair into a countifs formula.

=COUNTIFS({DAX Ambient Inventory Range 2}, ISDATE(@cell))

=COUNTIFS(({DAX Ambient Inventory Range 2}, NOT(ISDATE(@cell))). We could have also Counted project names and subtracted the ISDATES from that.

If you're looking for non blanks, you can always use <>"" which is an easy way to write not blank. Like the rest of the community, I'm here to help if you need it, just call me out.

Kelly

Kelly Moore

Hey @Barbara Witt

Try this

=COUNTIFS([your tbd column]:[your tbd column],OR(@cell = "tbd", @cell = ""))

Instead of @cell="" you can also use ISBLANK(@cell)

cheers,

Barbara Witt

Thanks, Kelly. That is what I am looking for. Is there something like ISNOTBLANK or HASAVALUE?

For another scenario I want to count how many project numbers I have if the date field is not empty (or if the date field has a date).

=COUNTIFS([your tbd column]:[your tbd column, ISDATE({DAX Ambient Inventory Range 2}))

=COUNTIFS([your tbd column]:[your tbd column, ISNOTDATE({DAX Ambient Inventory Range 2}))

The above does not work. How could this work?

I very much appreciate your continued support, Barbara

Kelly Moore

Hey Barbara

Is your COUNTIFS formula going on the same sheet or a cross referenced sheet? I ask because you have mixed the syntax of the ranges between same sheet and cross referenced sheets

When on the same sheet, we reference columns using [column name]:[column name].

For retrieving data from a different sheet, a cross-sheet referenced is created and it will be shown as {range} (As a good practice, edit the generic smartsheet range name to be sheet name -column name)

You're right that ISDATE will weed out both blanks and text (eg, 'tbd') in a date field.

COUNTIFS have the syntax of (range1, criteria1, range2, criteria2, range3, criteria3, etc). You always have to put in the range, criteria pair into a countifs formula.

=COUNTIFS({DAX Ambient Inventory Range 2}, ISDATE(@cell))

=COUNTIFS(({DAX Ambient Inventory Range 2}, NOT(ISDATE(@cell))). We could have also Counted project names and subtracted the ISDATES from that.

If you're looking for non blanks, you can always use <>"" which is an easy way to write not blank. Like the rest of the community, I'm here to help if you need it, just call me out.

Kelly

Barbara Witt

Yes, Kelly. This did it. Thank you very much.