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!

Find more posts tagged with

Countif

dates

Accepted answers

Nic Larsen

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.

All comments

Nic Larsen

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 <>.

PerDeSi

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.

Nic Larsen

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.

Paul Newcome

"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?

PerDeSi

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!

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.