# Formula to Count If by date

Options
edited 06/18/24

Hi, im trying to write a Count if formula by a date. IE, 7/15/24 , 7/16,24 and so on.
I have a separate sheet that pulls in task and date info by department from timelines, and the date of the task. My goal is to show a daily roll up of tasks for the department, by day

At the moment, ive got the below, and its returning as #UNPARSEABLE

• ✭✭✭✭✭✭
edited 06/18/24
Options

You are trying to search for a text field in a Date column, hence it cannot be done.

Create another text/number column called 'Date Helper' and then type this formula in to convert the date into text

Make this a column formula to convert all the dates.

Then the formula for counting each date would be:

=COUNTIF({Date Helper}:{Date Helper}, "07/15/24")

If this helped please upvote, it helps me!

• Options

@JIDEATTURRA Thank you for this.
Is this formula added to the origin sheet, or the roll up ? On the origin sheet, I am trying to pull instances of the start date. Attached a picture.
Unless, this is a formula that I add to the roll up sheet? Can you confirm its the case, and if it should link to the origin?
Thank you!

• ✭✭✭✭✭✭
Options

Original sheet.

The first formula would be (copy exactly, there is no need for a date in the speech marks) the below in a Date Helper column - this should be the date you are trying to track - if it’s the start date then it would be the below:

={Start}@row + ""

Then in the roll up sheet.. have a list of dates on the left called ‘Days’ in a text/number column..

07/15/24

07/16/24

07/17/24

And so forth..

Then have a column called count, which is a text/number to the right of this. The formula would be the below (the Date Helper:Date Helper is a cross sheet reference to the original sheet - be sure to set this up and select that column)

=COUNTIF({Date Helper}:{Date Helper}, "Days@Row")

• edited 06/18/24
Options

THANK YOU! The formula on roll up sheet ended up being =COUNTIF({Date Helper}, "Days@Row")

But it worked!

• ✭✭✭✭✭✭
Options

Excellent - yes for the cross sheet reference it will auto fill to whatever you name it opposed to the column:column - you can use this reference again in another formula just by typing {Date Helper} too (The reference is saved to the sheet!)

Glad to of helped you, have a great day!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!