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
Answers
-
What is the end goal for counting these columns?
-
To count how many people have been trained in the item
-
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?
-
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
-
Are you able to provide some screenshots (mock data is fine) for context?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!