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
-
{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]
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
{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.
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!