COUNTM and/or COUNTIFS formula and checkboxes

I have a project plan that tracks root causes of variances for milestone tasks. The variance root cause is a multi-select drop down list. Milestone tasks are identified using a checkbox.

I am trying to count the total number of root causes for all milestone tasks. I do not want to include root causes that may been identified for non-milestone tasks. In the example below, the answer would be 4.

I created references for the Root Cause and Milestone columns so I can collect the data on another sheet for reporting.

I tried using the following COUNTM formula but not getting the correct count.

=COUNTM({Root Cause}, {Milestone}, 1)

I also tried using COUNTIFS but it only counts 1 root cause for each milestone.

=COUNTIFS({Root Cause}, <>"", {Milestone}, 1)

Thank you in advance!

Find more posts tagged with

Accepted answers

Paul Newcome

Try this:

=COUNTM(COLLECT({Root Cause}, {Milestone}, @cell = 1))

SteveE

That works!

Thank you very much Paul!!!

All comments

Paul Newcome

Try this:

=COUNTM(COLLECT({Root Cause}, {Milestone}, @cell = 1))

SteveE

That works!

Thank you very much Paul!!!

Paul Newcome

Happy to help. 👍️

SteveE

I have a follow up question:

I would like to use the same logic, but specify a particular root cause (e.g., Scope Change).

I tried this variation, but the count is always 1.

=COUNTM(COLLECT({PP Root Cause}, "Scope Change", {PP Milestone}, @cell = 1))

Paul Newcome

@SteveE Your syntax is off. You need to repeat the root cause range one more time before entering the criteria.

SteveE

Thanks Paul. In this instance root cause is a reference to an entire column range in a different sheet. Won't repeating the reference return errors?

=COUNTM(COLLECT({PP Root Cause}:{PP Root Cause}, "Scope Change", {PP Milestone}, @cell = 1))

Paul Newcome

You would need to use the proper syntax.

criteria to collect, 1st criteria range, 1st criteria, 2nd criteria range, 2nd criteria

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.