Basic Formula

How can I write a working formula to count number of all cells in a column (excluding blanks) of a different sheet that don't equal a certain date?

Example:

COUNTIF(Referencedsheet_ColumnName(all but blanks), <>"12/20/1900")

Thank you

Sorry it is too basic but I am a beginner.

Best Answers

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Nadia Moreno

    The COUNT function should already exclude blanks from the calculation, but if you want to be extra sure, we can build this in by using COUNTFS (plural, with an S!)

    Try this:

    =COUNTIFS({Referenced Column}, <> "", {Referenced Column}, <> DATE(1900, 12, 20))

    <> "" says not blank

    Then I used the DATE function to specify a date. Keep in mind that this is assuming the {Referenced Column} is a date type of column. If it's a text column, then you'll want to use "12/20/1900" like you had in your example above.

    Let me know if this worked for you! If not, it would be helpful to see a screen capture of your sheet, but please block out any sensitive data.

    Cheers,

    Genevieve

  • Hello Genevieve,

    Thank you so much. With your assurance that COUNTIF excludes blanks cells, I used the COUNTIF function and it worked but when I use it to reference a column in a cross-referenced sheet it returns an error message. Then if I select the column instead of the column name, it enters a "Range 1" or "Range 2" and returns the right value. So COUNTIF({Employees Start Date}:{Employees Start Date}), <>DATE(1941, 1, 1)) doesn't work. By {Employees Start Date} I mean Start Date column in Employees sheet.

    I need to use the column name is the function rather that a range.

    Thank you again.

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 11/16/20 Answer ✓

    {Employees Start Date}:{Employees Start Date}

    is incorrect. Other sheet references do not have a second part to their range. Leave it as:

    {Employees Start Date}

    If the reference is on the same sheet, use brackets.

    [Employees Start Date]:[Employees Start Date]

  • L@123,

    Thank you very much.

    Regards.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!