COUNTIFS using dates and reference sheets

Looking for some help with this formula. I am either getting an unparseable or a 0 when I can manually see there is a match to be counted. I'm using the following formula to try to count the instances in another sheet that match the row item, ex: name of location, if they occured before today's date. (I've set Column3 as a date column formula for TODAY - I've also tried putting TODAY directly in the formula but that was giving me more trouble)

Formula used:

=COUNTIFS({reference sheet Range 9},<[Column3]67, {reference sheet Range 1},[Reporting Topic]@row])

Totals sheet with formula:

Reference Sheet to be counted on the final sheet. Start date is listed as a date, target location is a dropdown single select.

What am I missing? Thanks!!

Find more posts tagged with

Accepted answers

Paul Newcome

You still have a closing square bracket at the end that needs removed.

All comments

Paul Newcome

You need to get rid of the double closing square brackets at the end of the formula.

Lindsey B

Tried that and updated the formula shown above - still getting "unparseable"

NicoLHC

@Lindsey B , please share a copy of the sheet with me ,i can help .

Paul Newcome

Are you able to provide a screenshot of the sheet with the formula open in a cell as if you are about to edit it?

Lindsey B

In the number of targets column, this is my current formula

Paul Newcome

You still have a closing square bracket at the end that needs removed.

Lindsey B

Ah yes sorry - I'm using a dummy line and a real line in my sheet to prevent sharing all the data or names and missed updating the real line. My mistake! Thank you for your help!

After this formula change, I was still getting a 0. I finally realized I was comparing a date field and a dropdown list for my date criteria. Changing the dropdown list to a date field got me to the correct data!

NicoLHC

@Lindsey B i hope it understood correct for me its working please see….

´=COUNTIFS(Dates:Dates, <[Coloumn3]@row, Location:Location, [Reporting Topic]@row)´

Yellow is your reference Sheet , Blue you Target , orange the result ( I suggest to out names on the coloumn you are referencing otherwise it will be a mess later, Trust me ….

Bildschirmfoto 2024-09-13 um 21.31.07.png

NicoLHC

@Lindsey B so now all resolved with my formula ?

Paul Newcome

@Lindsey B Glad you were able to get that part sorted. Yes. Data types play a big role in formulas and functions. Even if they LOOK the same, they could be stored on the back-end very differently.

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.