Formula to Count If by date

Options

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
=COUNTIF({Writing Tasks}:{Writing Tasks}, "07/15/24")

Thank you in advance!

Answers

  • JIDEATTURRA
    JIDEATTURRA ✭✭✭✭✭✭
    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

    ={Writing Tasks}@row + ""

    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!

  • Anastasia Blades
    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!

  • JIDEATTURRA
    JIDEATTURRA ✭✭✭✭✭✭
    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")

    Please upvote my answers, it helps me.


  • Anastasia Blades
    edited 06/18/24
    Options

    @JIDEATTURRA

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

    But it worked!

  • JIDEATTURRA
    JIDEATTURRA ✭✭✭✭✭✭
    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!