Countif after certain date

Hi!

I'm trying to create a formula that will count the number of rows that have a "Delivery Date" after 1/1/2019. Any row with a date prior to that should not be counted. I also do not want blank rows counted.

In the screen capture attached, you'll see all but one populated row should be counted.

Any suggestions on a formula I could use? I appreciate any assistance you can provide!

Josh

Find more posts tagged with

Formulas

Comments

Jason Tarpinian

I don't know how to input hard dates within SmartSheet formulas, so I have a solution by adding a new column. Call it whatever (I used "Anchor Date" to test it out), and copy/paste your 1/1/19 date into the entire row) then use:

=COUNTIFS([Delivery Date]$1:[Delivery Date]$7, [Delivery Date]1 <> "", [Delivery Date]$1:[Delivery Date]$7, [Delivery Date]1 > [Anchor Date]1)

You can drop this formula through the whole column if you'd like. And every row would show the total. If you didn't want the blank rows to show any number edit the formula to exclude blanks:

=IF([Delivery Date]1 = "", "", COUNTIFS([Delivery Date]$1:[Delivery Date]$7, [Delivery Date]1 <> "", [Delivery Date]$1:[Delivery Date]$7, [Delivery Date]1 > [Anchor Date]1))

L_123

to input a hard date you use the Date Reference

=Date(2019,12,31)

will return december 31, 2019

This can be used inside of formulas as well

JAA

Great! I accomplished what I needed with the below formula.

=COUNTIFS([Delivery Date]:[Delivery Date], [Delivery Date]1 <> "", [Delivery Date]:[Delivery Date], [Delivery Date]1 > DATE(2019, 1, 1))

JAA

Thank you! This in addition to the above comment got me closer to where I'd like to be.

Jason Tarpinian

I will have to remember that one, it's not often I need to use it, but for those couple of times!

JAA

If I wanted to add in that the date would also have to be before today's date (so Jan 1st through today), what would you suggest?

L_123

if you want it to always be the current year and not have to update the formula annually you can use this

date(year(today()),1,1)

JAA

Found what I needed:

=COUNTIFS([Delivery Date]:[Delivery Date], [Delivery Date]1 <> "", [Delivery Date]:[Delivery Date], [Delivery Date]1 > DATE(2019, 1, 1), [Delivery Date]:[Delivery Date], [Delivery Date]1 < TODAY())

Paul Newcome

You can also use the AND function for your criteria along with @cell references.

=COUNTIFS([Delivery Date]:[Delivery Date], AND(ISDATE(@cell), YEAR(@cell = YEAR(TODAY()), @cell < TODAY()))

You are only referencing one range. Your date range. You can use the AND function to combine all of the criteria to keep from having to repeat the range.

In this particular formula we are saying to look at the [Delivery Date] column and count all cells that meet the following criteria:

Is a date

AND

Has the current year

AND

is before today.

Here is a page that describes the various functions used in Smartsheet formulas.

https://help.smartsheet.com/functions

Here is a page that helps with troubleshooting errors with formulas and functions.

https://help.smartsheet.com/articles/2476176-formula-error-messages?_ga=2.163823034.487922610.1554120052-1302373248.1552411124

And if you look through the templates, there is one called "Smartsheet Formula Examples". This is an actual Smartsheet with all of the functions explained and shown in use along with a few hints and tips. Because it is a sheet, it is interactive. You can play around with the formulas to see what happens. If you mess something up, you can either use the Undo button or simply delete the sheet and re-download the template.

JAA

Great info! I love the tip about the available template.

Sign in to join the conversation!

Quick Links

Trending discussions

Help creating formula to return a date in a column that is closest to today's date
I need help creating a formula that references the "Due in Payroll" column I have which is a series of dates. I would like the formula to look at that date column, and return whichever date in that column is closest to today's date. I've tried several formulas, all come up invalid column type, unparseable invalid…
Formula to Convert Scientific Notation to Numerical Value
Hi, I am trying to create a sheet from data that is originally held in .csv files. My goal is to make it so that transferring the data to my sheet should be as simple as just copy and paste. My issue is that two of the fields that I need to copy over are reported in scientific notation (1.3E+004) and I am trying to figure…
Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…