Basic Formula

Nadia Moreno

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.

L_123

{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]

Genevieve P.

Hi @Nadia Moreno

I'm glad you could figure this out with @L@123 's help!

See Here for an article on referencing columns in formulas that may clarify this a bit more for your future formula building.

Cheers,

Genevieve

Genevieve P.

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

Nadia Moreno

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

{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]

Nadia Moreno

L@123,

Thank you very much.

Regards.

Genevieve P.

Hi @Nadia Moreno

I'm glad you could figure this out with @L@123 's help!

See Here for an article on referencing columns in formulas that may clarify this a bit more for your future formula building.

Cheers,

Genevieve