Count based on Multiple Criteria & Dates
Hi,
I am attempting to create a formula that would =
Count the number of training items completed per team member and between two dates.
I created this formula, it is giving me all Zeros:
=COUNTIFS([Actual End Date]:[Actual End Date], <=DATE(2024, 1, 1), [Actual End Date]:[Actual End Date], >=DATE(2024, 12, 30), Status:Status, "Training Completed - 100%", [Team Members]:[Team Members], [Team Members]@row)
Can someone provide guidance on what I am doing wrong?
Also, how can i use the output of this formula to produce a status - so if the output of the formula above is less than 5 = output would be "Needs more training". Or do I need to create a seperate column for that?
Thank you!
Best Answer
-
If there are blanks in the date column that will impact what it can count based on the criteria you have listed. Would it be more accurate to remove the 2nd Date constraint so it's only looking to count for training that has happened since >= 1/1/24?
I didn't see your 2nd question, but you could add a column that takes the result of this column and returns a status. So, if this column is <5, it returns "need more training", for example.
Answers
-
The only thing that jumps out is the Dates. The way it reads is less than 1/1/24 and greater than 12/30/24… I'd swap the direction of the <>.
-
Thanks Nic!
That helped a bit, it now showing a number instead of an error.
Unfortuantly, it is now 0 on all the rows but showing a 1 on the ROWS where there is an date in the [Actual End Date] column.
-
If there are blanks in the date column that will impact what it can count based on the criteria you have listed. Would it be more accurate to remove the 2nd Date constraint so it's only looking to count for training that has happened since >= 1/1/24?
I didn't see your 2nd question, but you could add a column that takes the result of this column and returns a status. So, if this column is <5, it returns "need more training", for example.
-
"Unfortuantly, it is now 0 on all the rows but showing a 1 on the ROWS where there is an date in the [Actual End Date] column."
Are you able to provide a screenshot?
-
Thanks @Nic Larsen & @Paul Newcome for your response.
I re-wrote the formula (to exclude blanks) and realized I was selecting the wrong column for Team Members.
Thank you for your help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.7K Get Help
- 435 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …