COUNTM Formula

Orlando Mercado

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({MH-Work Prioritization Log Range 2}, {Benefits}, "Reduce Internal Pain Point")

=COUNTM({Benefits}, "Efficiency Gain")

Kelly Moore

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 multi-select column, you should be able to just use "In Progress". To verify - your {Benefits} range and {MH-Work Prioritization Log Range 1} is on the same sheet? If yes, your {MH-Work 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

Kelly Moore

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 multi-select column, you must use the HAS or CONTAINS functions.

Try this

=COUNTIFS({Benefits}, HAS(@cell, "Efficiency Gain")

Does this get you what you need?

Orlando Mercado

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", {MH-Work 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", {MH-Work 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 MH-Work Prioritization Log

=COUNTIFS({Benefits}, CONTAINS(@cell, "Efficiency Gain", {Status}, CONTAINS(@cell, "In Progress")))

Kelly Moore

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 multi-select column, you should be able to just use "In Progress". To verify - your {Benefits} range and {MH-Work Prioritization Log Range 1} is on the same sheet? If yes, your {MH-Work 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

Orlando Mercado

It worked. Thank you so much for your help!