How can I count date columns but exclude some

So I want to count a range of columns that have dates in them but exclude certain columns if the date is part of a formula.

The use case is a training matrix.

Each person has 2 columns.

eg - "John Doe" is a date column and will get set a date when John Doe is trained to an item (Items are rows)

"John Doe retrain date" is a date column and value is set by adding "Retrain cadence" cell which is a duration and "John Doe" cell which is a date.

So row 1 might be "Document X" (Colum name "Item" ) which John Doe needs to train to.

Say John Doe trains to item today column is set to "05-Feb-25" .

Retrain cadence is "5d" so "John Doe retrain date" auto sets to "10-Feb-25".

I want to be able to count only the trained date ie "John Doe" value. And want to count this for multiple users (each user will have 2 columns).

So I want to only count the trained date column values and exclude the retrain date column values.

Hope this makes sense

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

What is the end goal for counting these columns?

AJ Barry

To count how many people have been trained in the item

Paul Newcome

I understand that, but are you planning to put them on a dashboard? If so, are you wanting just basic counts, or a more chart like presentation?

It sounds like you have 3 columns per person? Is each person's columns grouped together and in the same order from left to right? Are you able to add a "helper" row to this sheet?

AJ Barry

Intent is to feed into a dashboard yes so I can display % trained etc.

3 columns per person yes but 1 column ("Retrain cadence") is generic to all persons. I managed to figure a workaround where I add all 'retrain date' columns to the right and have all Persons columns together then just select the range across the persons columns. I separate the Persons and retrain date columns with a 'dud' column called 'Add 'Name' to left'. This remains blank but is included in the count so all future Person column additions are included

=COUNTIF([Name A]@row:[Add 'Name' to Left]@row, ISDATE(@cell ))

Can you tell me how I could count the rows and then work out % training complete based on Number of persons and number of training items (rows)?

And present in dashboard

Paul Newcome

Are you able to provide some screenshots (mock data is fine) for context?

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.