Welcome!

countifs formula to sum not blank dates

Hello ~ I'm new to the community and working with the COUNTIFS formula. Help would be much appreciated if you can see the error of my ways. What I want to do seems simple - I have a "Date Completed" column, defined as a Date type, and want to count how many times it's been entered in the column. I saw a thread earlier that addressed a similar topic, but I've so far been unable to get it to work. Here's the formula I'm working with:

=COUNTIFS([Date Completed]:[Date Completed], IFERROR(YEAR(@cell), 0) >= 2019)

I'd like it to be more generic, using a not isempty, but so far haven't been able to get it to work. The formula is expecting a #DATE EXPECTED (presumably since the column is a Date type) - is there a way to do this?

Thank you

Find more posts tagged with

Education

Comments

Paul Newcome

Try something like this...

=COUNTIFS([date completed]:[date completed], ISDATE(@cell))

Mike Wilday

Try this. It will find every blank cell in the date completed column.

=Countifs([date completed]:[date completed], ISBLANK(@cell))

Kelly M

Thank you, Paul, for you comment. It's giving a #DATE EXPECTED error. I think I need the IFERROR to catch that, but so far, haven't solved it.

Kelly M

Hi Mike, and thank you too, for you comment. It's also giving a #DATE EXPECTED error. I *think* I need to use an ISERROR to pick up and catch the error, but so far, no luck. I'm also working with trying a not(isblank(@cell))

Paul Newcome

The cell you are putting the formula into must be in a date type column. Try this...

=COUNTIFS([date completed]:[date completed], ISDATE(@cell)) + ""

Kelly M

OK Paul, first, you're brilliant and thank you, that worked. Now second, I'm trying to understand why? What does the + "" do? How would you read this in English? Count the row if the date completed is a valid date anywhere in the column? but what's the + "" for?

And thank you again!

Mike Wilday

The +"" turns the result of the function into a text... instead of requiring it to be a date. The formula will no longer expect to produce a date output. It is pretty genius. Good job Paul!

Kelly M

Ah - Thanks, Mike, for the clarification - and thanks Paul ~ that is a great workaround.

Paul Newcome

Thanks Mike for going ahead with the explanation.

I use the + "" a lot in date, checkbox, and symbol fields. One of my most typical uses (while not exactly the same but very close no less) is when I build out a date table.

We can start a project whenever we want so long as certain deliverables are met before a specific date. My people don't like blank cells when looking at projected dates, so for that first set of deliverables, the Projected Start is - and the Projected Finish is the Friday before Week 1.

If you just type a - into a date type cell, you get the error. But if you turn it into "text" it will be displayed. So the Projected Start for Week 0 is actually

="-"

A lot of walls have felt the wrath of my forehead getting to this relatively simple solution, so this one tends to stick with me a bit. HAHAHA

Paul Newcome

Kelly,

Always happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

I am getting an error message when attempting to export to excel
I am attempted to export to excel. I have two different smartsheet documents to export. One of them exported to excel fine, but not the other. This happened a few weeks ago. I make sure all columns were the correct size & no cells were hidden. "We have found a problem with (file) Do you want us to try and recover as much…
Need Help w/ Report View - Nothing Is Showing
Hello Smartsheet Community, I need some help with a report that I created and shared with a customer. The customer is not seeing any of the information that is pulling into the report. See screen shot below that the customer texted me. I do have the report filter only showing the customer their product pulling from our…
How can I hard code a cell based on a trigger?
BLUF: When I create an Invoice, I need the cell-link to my live price data to be severed. I currently have a Price sheet for my products that is updated frequently. I use Control Center to create Projects, which includes an Invoice sheet. I have a dropdown on my invoice sheet to select a product, and then I use Index…