COUNTM and/or COUNTIFS formula and checkboxes

SteveE

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!

Paul Newcome

Try this:

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

SteveE

That works!

Thank you very much Paul!!!

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