COUNTIFS FORMULA when date is today or in the past

Ed Gadd

I'm having invalid operation being returned on this formula. can anyone help please?

=COUNTIFS(@cell <= TODAY([Column6]2), {TIME RECORDING 7}, $[Column7]@row)

I'm trying to count the number of times a word appears in a column when the date is today or in the past.

Column6]2 is a date column showing todays date and is linked cell to my date master sheet.

Column7]@row is the cell populated with the time recording word to be counted.

Hope that makes sense? Many thanks in advance

Find more posts tagged with

Accepted answers

All comments

SoS | Dan Palenchar

Hello @Ed Gadd

It appears you have some issues with the formula structure.

TODAY([Column6]2) will not work since you are referencing a date column where there should be a number (See below)

The use of @cell does not appear to be correct. If you are trying to evaluate if a date is less than today you would structure this is =COUNTIFS([Date Field] < TODAY(),…)
The second portion with the cross sheet reference needs a way to evaluate the range you selected which I assume is a column. This would be something like …{TIME RECORDING 7}, CONTAINS([Column7]@row, @cell)

However, I think you will be better off using COUNT(COLLECT()). I would try something like this to start. I am writing this off the type of my head and not sure of your Sheet structure so I don't expect it to work straight away:

=COUNT(COLLECT({TIME RECORDING 7}, {TIME RECORDING 7}, CONTAINS([Column7]@row, @cell), [Column6]:[Column6], IFERROR(@cell < TODAY(), 0)))

Ed Gadd

Hi @SoS | Dan Palenchar

Thanks for the help. The formula is close, however everything comes out as a 1 irrespective if the words appear in the column once, twice etc or not at all?

Thanks again for the help

Ed Gadd

Hi all,

I'm still struggling with this formula, can anyone help. i only seems to get one as a response.

=COUNT(COLLECT({TIME RECORDING 7}, {TIME RECORDING 7}, CONTAINS($[Column7]@row, @cell), $[Column6]$2, IFERROR(@cell < = TODAY (), 0)))

[Column6]$2 is a cell which contains todays date.

Thanks in advance for any advice.

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.