COUNTM Formula
How can my COUNTM Formula only capture those projects where efficiency gain is 1 of the benefits selected for that project. Project currently have more than 1 benefit such as "Efficiency Gain".
I am currently entering the formulas below, but not sure what else to do.
=COUNTM({MHWork Prioritization Log Range 2}, {Benefits}, "Reduce Internal Pain Point")
=COUNTM({Benefits}, "Efficiency Gain")
Best Answer

Hey @Orlando Mercado
I would have expected your first countifs in the post above would have worked  although you don't need the HAS function with your Status column since it's not a multiselect column, you should be able to just use "In Progress". To verify  your {Benefits} range and {MHWork Prioritization Log Range 1} is on the same sheet? If yes, your {MHWork Prioritization Log Range 1} is equal to the Status column?
I am assuming you're getting zero as your response instead of an error message? Or a count value that is not the expected result?
As a test, remove the Benefits and it's criteria from your COUNTIFS. What value does it give you use your first formula above?
Kelly
Answers

Hey @Orlando Mercado
A COUNTM gives a count of the number of multi select responses are in a range. I'm not sure this is exactly what you are trying to do. When dealing with a multiselect column, you must use the HAS or CONTAINS functions.
Try this
=COUNTIFS({Benefits}, HAS(@cell, "Efficiency Gain")
Does this get you what you need?

I am trying to capture a count for benefits that contain efficiency gains as part of the cells under benefits and are in the status of "In Progress".
I tried to use this formula.
=COUNTIFS({Benefits}, HAS(@cell, "Efficiency Gain", {MHWork Prioritization Log Range 1}, HAS(@cell, "In Progress")))
I also tried using the formula below to capture both cells with Efficiency Gaina and In Progress.
=COUNTIFS({Benefits}, CONTAINS(@cell, "Efficiency Gain", {MHWork Prioritization Log Range 1}, CONTAINS(@cell, "In Progress")))
I've also tried to use STATUS in the formula since the column is called that. The sheet is called MHWork Prioritization Log
=COUNTIFS({Benefits}, CONTAINS(@cell, "Efficiency Gain", {Status}, CONTAINS(@cell, "In Progress")))

Hey @Orlando Mercado
I would have expected your first countifs in the post above would have worked  although you don't need the HAS function with your Status column since it's not a multiselect column, you should be able to just use "In Progress". To verify  your {Benefits} range and {MHWork Prioritization Log Range 1} is on the same sheet? If yes, your {MHWork Prioritization Log Range 1} is equal to the Status column?
I am assuming you're getting zero as your response instead of an error message? Or a count value that is not the expected result?
As a test, remove the Benefits and it's criteria from your COUNTIFS. What value does it give you use your first formula above?
Kelly

It worked. Thank you so much for your help!
Help Article Resources
Categories
Check out the Formula Handbook template!