Formula to Count If by date

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!

Find more posts tagged with

Accepted answers

All comments

Ide AirTrunk

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

@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!

Ide AirTrunk

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

@JIDEATTURRA

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

But it worked!

Ide AirTrunk

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.