Sheet Formula

nick.wilson

Happy 2024 everyone, I hope your year is off to a great start! I'm back requesting help to get some formulas working. I am trying to use the countif formula to count a column in a sheet. I am trying to get it to count all the dates in the column, but I also want to use to the formula to count all the rows with that same column where there is no date. In other word I want to count all of my projects that have a completion date, and then count all my projects that do not. Lastly I want to use the countif formula to count all my projects year to date. Can someone assist with this?

John_Foster

Hi @Nick Wilson ,

I believe the three formulas you need are as follows:

1. All the rows where there is a date - =COUNTIF([DateColumn]:[DateColumn],@cell<>"")
2. All the rows where there is not a date - =COUNTIF([DateColumn]:[DateColumn],@cell="")
3. All the rows where the date is this year - =COUNTIFS([Date Column]:[Date Column], @cell <> "", [Date Column]:[Date Column], YEAR(@cell) = YEAR(TODAY()))

It is worth noting that the second formula as is will return all blank rows at the end of a sheet so using the formula below referencing a cell which should always have a value as per the formula below.

=COUNTIFS([DateColumn]:[DateColumn], @cell = "", [OtherColumn]:[OtherColumn], @cell <> "")

Hope this helps!

John

John_Foster

Hi @Nick Wilson ,

I believe the three formulas you need are as follows:

1. All the rows where there is a date - =COUNTIF([DateColumn]:[DateColumn],@cell<>"")
2. All the rows where there is not a date - =COUNTIF([DateColumn]:[DateColumn],@cell="")
3. All the rows where the date is this year - =COUNTIFS([Date Column]:[Date Column], @cell <> "", [Date Column]:[Date Column], YEAR(@cell) = YEAR(TODAY()))

It is worth noting that the second formula as is will return all blank rows at the end of a sheet so using the formula below referencing a cell which should always have a value as per the formula below.

=COUNTIFS([DateColumn]:[DateColumn], @cell = "", [OtherColumn]:[OtherColumn], @cell <> "")

Hope this helps!

John

nick.wilson

That did the trick! THX!