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!

Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!